convert LINQ to sql query
  • How do you convert this LINQ to sql query?
      var query = from q in context.AID
                                    .Include("premises")
                                    //.Include("LK_Species") 
                                    .Where(x => x.PremisesID == pID )//&& x.SpeciesID == x.LK_Species.ID)
                                select (new {
                                    q.PremisesID,
                                    q.AID_ID,
                                    q.AID_TypeText,
                                    q.AID_Prefix,
                                    q.AID_Start,
                                    q.AID_End,
                                    q.AID_Count,
                                    q.AID_Date,
                                    q.Premises.StateID,
                                    q.Premises.NationalID,
                                    q.Premises.Name,
                                    q.AID_Type.Type,
                                    Start = q.AID_Prefix + q.AID_Start,
                                    End = q.AID_Prefix + q.AID_End,
                                    Contact = q.Premises.PremisesContactLinks.FirstOrDefault().Contacts.FullName,
                                    q.Premises.PremisesAddresses.FirstOrDefault().County,
                                    q.SpeciesID,
                                    text = q.LK_Species.text  
                                });

  • 6 Comments sorted by
  • Select q.PremisesID,
            q.AID_ID,
            q.AID_TypeText,
            q.AID_Prefix,
            q.AID_Start,
            q.AID_End,
            q.AID_Count,
            q.AID_Date,
            p.StateID,
            p.NationalID,
            p.Name,
            q.AID_Type,
            q.AID_Prefix + q.AID_Start as Start,
            q.AID_Prefix + q.AID_End as End ,
            (Select top 1 FullName from PremisesContactLinks pc where pc.PremisesID = pc.PremisesID)
            (Select top 1 County from PremisesAddresses pa where pa.PremisesID = pc.PremisesID)
            q.SpeciesID,
            q.LK_Species as [text]
     from AID q
    inner join premises p on p.PremisesID = q.pID



    hope this will help you some how other wise please share me your database then i will give you a complete solution
  • hi jaguar can u please send me the tables where the data is coming so i can give u more aqurate result query
  • @atirnaveed here are the tables.

    Thanks
    aid.PNG
    267 x 257 - 10K
    contacts.PNG
    274 x 290 - 14K
    premisecontactlinks.PNG
    253 x 132 - 6K
    premises.PNG
    267 x 291 - 12K
    premisesaddress.PNG
    261 x 242 - 10K
  • I was able to convert this using SQL Profiler
    "SELECT 
    [Filter1].[AID_ID] AS [AID_ID], 
    [Filter1].[PremisesID1] AS [PremisesID], 
    [Filter1].[AID_TypeText] AS [AID_TypeText], 
    [Filter1].[AID_Prefix] AS [AID_Prefix], 
    [Filter1].[AID_Start] AS [AID_Start], 
    [Filter1].[AID_StartNum] AS [AID_StartNum], 
    [Filter1].[AID_EndNum] AS [AID_EndNum], 
    [Filter1].[AID_End] AS [AID_End], 
    [Filter1].[AID_Count] AS [AID_Count], 
    [Filter1].[AID_Date] AS [AID_Date], 
    [Filter1].[StateID1] AS [StateID], 
    [Filter1].[NationalID1] AS [NationalID], 
    [Filter1].[Name1] AS [Name], 
    [Filter1].[Type1] AS [Type], 
    [Filter1].[AID_Prefix] + [Filter1].[AID_Start] AS [Start], 
    [Filter1].[AID_Prefix] + [Filter1].[AID_End] AS [End], 
    [Extent6].[FullName] AS [FullName], 
    [Limit2].[County] AS [County], 
    [Filter1].[SpeciesID] AS [SpeciesID], 
    [Extent8].[text] AS [text]
    FROM      (SELECT [Extent1].[AID_ID] AS [AID_ID], [Extent1].[PremisesID] AS [PremisesID1], [Extent1].[AID_Prefix] AS [AID_Prefix], [Extent1].[AID_Start] AS [AID_Start], [Extent1].[AID_End] AS [AID_End], [Extent1].[AID_StartNum] AS [AID_StartNum], [Extent1].[AID_EndNum] AS [AID_EndNum], [Extent1].[AID_Count] AS [AID_Count], [Extent1].[AID_Date] AS [AID_Date], [Extent1].[AID_TypeText] AS [AID_TypeText], [Extent1].[SpeciesID] AS [SpeciesID], [Extent2].[StateID] AS [StateID1], [Extent3].[NationalID] AS [NationalID1], [Extent3].[Name] AS [Name1], [Extent4].[Type] AS [Type1]
    FROM    [dbo].[AID] AS [Extent1]
    INNER JOIN [dbo].[Premises] AS [Extent2] ON [Extent1].[PremisesID] = [Extent2].[PremisesID]
    LEFT OUTER JOIN [dbo].[Premises] AS [Extent3] ON [Extent1].[PremisesID] = [Extent3].[PremisesID]
    LEFT OUTER JOIN [dbo].[AID_Type] AS [Extent4] ON [Extent1].[AID_TypeID] = [Extent4].[AID_TypeID]
    WHERE @PremisesId= [Extent1].[PremisesID] ) AS [Filter1]
    OUTER APPLY  (SELECT TOP (1) [Extent5].[ContactID] AS [ContactID]
    FROM [dbo].[PremisesContactLinks] AS [Extent5]
    WHERE [Filter1].[PremisesID1] = [Extent5].[PremisesID] ) AS [Limit1]
    LEFT OUTER JOIN [dbo].[Contacts] AS [Extent6] ON [Limit1].[ContactID] = [Extent6].[ContactID]
    OUTER APPLY  (SELECT TOP (1) [Extent7].[County] AS [County]
    FROM [dbo].[PremisesAddresses] AS [Extent7]
    WHERE [Filter1].[PremisesID1] = [Extent7].[PremisesID] ) AS [Limit2]
    LEFT OUTER JOIN [dbo].[LK_Species] AS [Extent8] ON [Filter1].[SpeciesID] = [Extent8].[ID]

  • @PremisesId is a parameter that need to be declared
  • here is you can use this in simple way ..
    but the last column @q.LK_Species as [text] , i didn't find its table or column


    Select a.PremisesID,
            a.AID_ID,
            a.AID_TypeText,
            a.AID_Prefix,
            a.AID_Start,
            a.AID_End,
            a.AID_Count,
            a.AID_Date,
            a.Premises.StateID,
            a.Premises.NationalID,
            a.Premises.Name,
            at.[Type],
            a.AID_Prefix + a.AID_Start as Start,
            a.AID_Prefix + a.AID_End as [End] ,
            (
    Select top 1 c.FullName 
    from PremisesContactLinks pc  inner join Contact c on c.ContactID = pc.ContactID
    where pc.PremissesID = p.PremissesID
    )  as Contact,
    (
    Select top 1 pa.County 
    from PremisesAddresses pa 
    where pa.PremisesID = p.PremisesID
    ) as County,
    a.SpeciesID
     from AID a
    inner join premises p on a.PremisesID = p.PremisesID
    inner join AID_Type at on at.AID_TYPEID= a.AID_TYPEID

Howdy, Stranger!

It looks like you're new here. If you want to get involved, or you want to Ask a new Question, Please Login or Create a new Account by Clicking below

Login with Facebook

Tagged

Popular Posts of the Week

Top Posters Weekly

Optimum Creative