none
how to convert the following sql statement to linq? RRS feed

  • Question

  • select distinct lm.location_name from dbo.Location_mstr lm

    inner join

    practice_location_xref plx on lm.location_id = plx.location_id

    left join dbo.User_Location_Preferences lp on lp.location_id = lm.location_id and lp.delete_ind = 'N'

    where lm.delete_ind = 'N'

    and(user_id = @user_id or not exists(select 1

    fromdbo.User_Location_Preferences whereuser_id = @user_id and practice_id = @practice_id))

    and plx.practice_id = @practice_id

    and lm.location_type = 'L'

    order by 1

    This is what I have so far...

               

    var locations = (from lm in context.location_mstr.Where(lmcal => lmcal.delete_ind ==  "N"&& lmcal.location_type == "L")

    join plx in context.practice_location_xref on lm.location_id equals plx.location_id

                               

    join

    ulp incontext.user_location_preferences.Where(ulpcal => ulpcal.delete_ind == "N") onlm.location_id equalsulp.location_id

    into t from rt in t.DefaultIfEmpty()

                                

    orderby lm.location_name

                               

    selectnew


                                {

                                    lm.location_id,

                                    lm.location_name

                                }).ToList()


    • Edited by SKPillai Tuesday, January 9, 2018 9:17 PM
    Tuesday, January 9, 2018 9:06 PM

All replies

  • Hi SKPillai,

    Could you please provide related data table structure, I could not execute the T-SQL in my visual studio, I encounter a issue.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 10, 2018 7:55 AM
    Moderator
  • CREATE

    TABLE[dbo].[location_mstr](

    [location_id][uniqueidentifier]NOTNULL,

    [location_type][varchar](2)NULL,

    [location_name][varchar](40)NULL,

    [address_line_1][varchar](55)NULL,

    [address_line_2][varchar](55)NULL,

    [city][varchar](35)NULL,

    [state][varchar](3)NULL,

    [zip][varchar](9)NULL,

    [county_id][uniqueidentifier]NULL,

    [country_id][uniqueidentifier]NULL,

    [phone][varchar](10)NULL,

    [delete_ind][char](1)NOTNULL,

    CONSTRAINT[pk_location_mstr]PRIMARYKEYNONCLUSTERED

    ([location_id] ASC)

    )

    WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,FILLFACTOR=95)ON[NEXTGEN_INDEX_1]


    )

    ON[NEXTGEN_CORE]



    GO


     


    Wednesday, January 10, 2018 4:54 PM
  • CREATE

    TABLE[dbo].[practice_location_xref](


    [practice_id][char](4)NOTNULL,


    [location_id][uniqueidentifier]NOTNULL,


    [created_by][int]NOTNULL,


    CONSTRAINT[pk_practice_location_xref]PRIMARYKEYNONCLUSTERED


    (


    [practice_id]ASC,


    [location_id]ASC


    )

    WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,FILLFACTOR=95)ON[NEXTGEN_INDEX_1]


    )

    ON[NEXTGEN_CORE]



    GO

    Wednesday, January 10, 2018 4:55 PM
  • CREATE

    TABLE[dbo].[user_location_preferences](


    [practice_id][char](4)NOTNULL,


    [user_id][int]NOTNULL,


    [location_id][uniqueidentifier]NOTNULL,


    [seq_nbr][int]NOTNULL,


    [user_pref_id][uniqueidentifier]NOTNULL,


    [delete_ind][char](1)NOTNULL,


    CONSTRAINT[pk_user_location_preferences]PRIMARYKEYCLUSTERED


    (


    [practice_id]ASC,


    [user_id]ASC,


    [location_id]ASC,


    [seq_nbr]ASC,


    [user_pref_id]ASC


    )

    WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[NEXTGEN_CORE]


    )

    ON[NEXTGEN_CORE]



    GO

    Wednesday, January 10, 2018 4:55 PM
  • Hi SKPillai,

    Could you please provide some sample records, Based on your table structure, I create a test records, which encounter a error like this:

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 15, 2018 6:30 AM
    Moderator