none
Conversion of SQL Query to LINQ RRS feed

  • Question

  • Hello Experts

    I am trying to convert a sql query which has sub queries :

    SELECT sd.[HW-EOL-FY],COUNT(sd.[HW-EOL-FY]) from (

    SELECT *

    FROM SQLUTIL01.ServCheck.dbo.ManagedServer

    WHERE ServerID IN (

    SELECT ServerID

          FROM (

                SELECT

                      ServerName AS [SRV-Name],

                      MAX(ServerID) as ServerID

                FROM  SQLUTIL01.ServCheck.dbo.ManagedServer

                GROUP BY ServerName

          ))) ms

    left join serverdata sd

    on ms.ServerName=sd.ServerName

    group by sd.[HW-EOL-FY]

    I was trying something like this:

    from

     

     

    p in ManagedServers

    join

     

     

    q in ServerData

    on

     

     

    p.ServerName equals q.ServerName

    where

     

     

    q.ORG_Domain=="ASM"

    group

     

     

    q by new {q.HW_EOL_FY} into EOL

    select

     

     

    new {EOL.Key.HW_EOL_FY,Count=EOL.Count()}

    This doesnt seems working .... Please Help ?

    Thanks

    Friday, June 18, 2010 4:42 AM

Answers

  • Hello,

    As far as I know, there is no In operator in linq to sql standard operators, instead, you could use Any operator. For the standard operator list: http://msdn.microsoft.com/en-us/library/bb394939.aspx.

    For example, in your case:

    SELECT * FROM ManagedServer WHERE ServerID in (...)

    You could use the following istead:

    from ms in db.ManagedServer
    where db.Server.Any(s=>s.ServerID==ms.ServerID) // just for an example.
    select ms;

    Please have a try and let me know the result!
    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Monday, June 21, 2010 10:53 AM

All replies

  • You can use the LINQPAD or http://www.sqltolinq.com/home 


    Regards
    Nayan Paregi (MCTS)
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, June 18, 2010 8:57 AM
  • what do you mean by not working? do you get compile error? or the result is not what you want?
    Friday, June 18, 2010 5:20 PM
  • result is not correct and moreover I dont know how to write subquery in LINQ...

     

    here is the exact query that I am trying :

    SELECT

     

    sd.[HW-EOL-FY],COUNT(sd.[HW-EOL-FY]) from (

    SELECT

     

    *

    FROM

     

    ManagedServer

    WHERE

     

    ServerID IN (

    SELECT

     

    ServerID

     

    FROM (

     

    SELECT

    ServerName

    AS [SRV-Name],

     

    MAX(ServerID) as ServerID

     

    FROM ManagedServer AS ms0

     

    GROUP BY ServerName

     

    ) as ms1

     

    ) ) as ms

    left

     

    join serverdata sd

    on

     

    ms.ServerName=sd.ServerName

    where

     

    sd.[ORG-Domain]='ASM'

    group

     

    by sd.[HW-EOL-FY]

     

    The software provided above returns error after conversion....

    Please help ?

    Thanks

    Friday, June 18, 2010 9:15 PM
  • Hello,

    As far as I know, there is no In operator in linq to sql standard operators, instead, you could use Any operator. For the standard operator list: http://msdn.microsoft.com/en-us/library/bb394939.aspx.

    For example, in your case:

    SELECT * FROM ManagedServer WHERE ServerID in (...)

    You could use the following istead:

    from ms in db.ManagedServer
    where db.Server.Any(s=>s.ServerID==ms.ServerID) // just for an example.
    select ms;

    Please have a try and let me know the result!
    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Monday, June 21, 2010 10:53 AM