none
LINQ JOIN -- How to do left and inner join in same query RRS feed

  • Question

  • var query = (from r1 in dtMappedSectionNotEmpty.AsEnumerable()
                                     join r2 in dtData.AsEnumerable()
                                     on r1.Field<string>("Row").Trim().ToUpper() equals r2.Field<string>("RowCoordinate").Trim().ToUpper()
                                     join r3 in _Periods.AsEnumerable()
                                     on r2.Field<string>("StandardDate").Replace("A", string.Empty).Replace("E", string.Empty).Trim().ToUpper() equals r3.Replace("A", string.Empty).Replace("E", string.Empty).Trim().ToUpper()
                                     where r1.Field<string>("Tab").Trim().ToUpper() == strTab.Trim().ToUpper()
                                     select new BrokerData
                                     {
                                         RowNumber = r1.Field<string>("Row") ?? "0",
                                         TabName = r1.Field<string>("Matched Section") ?? "",
                                         StandardDate = r3.ToString(),
                                         BRTab = r1.Field<string>("Tab") ?? "",
                                         BRLineItem = r1.Field<string>("Broker Items") ?? "",
                                         Action = r1.Field<string>("Action") ?? "",
                                         StandardLineItem = r1.Field<string>("Matched Items") ?? "",
                                         StandardValue =r2.Field<string>("LineItemDateValue") ?? ""
                                     }).ToList();

    in my above code there are 2 datatable and one list. now i am not being able to compose left and inner join in same query.

    there will be left join between dtMappedSectionNotEmpty and dtData. there will be left join between r1 & r2

    again there will be inner join between dtData & _Periods means i want inner join between r2 & r3

    so please see my code and give me another set same LINQ code where left join and inner join will be there. thanks 

    • Edited by Sudip_inn Wednesday, June 10, 2020 9:56 AM
    Wednesday, February 19, 2020 2:15 PM

Answers

  • var query = (from r1 in dtMappedSectionNotEmpty.AsEnumerable()
                                     join r2 in dtData.AsEnumerable()
                                     on r1.Field<string>("Row").Trim().ToUpper() equals r2.Field<string>("RowCoordinate").Trim().ToUpper() into gj
    				 from subset in gj.DefaultIfEmpty()
                                    join r3 in _Periods.AsEnumerable()
                                     on r2.Field<string>("StandardDate").Replace("A", string.Empty).Replace("E", string.Empty).Trim().ToUpper() equals r3.Replace("A", string.Empty).Replace("E", string.Empty).Trim().ToUpper()
                                     where r1.Field<string>("Tab").Trim().ToUpper() == strTab.Trim().ToUpper()
                                     select new BrokerData
                                     {
                                         RowNumber = r1.Field<string>("Row") ?? "0",
                                         TabName = r1.Field<string>("Matched Section") ?? "",
                                         StandardDate = r3.ToString(),
                                         BRTab = r1.Field<string>("Tab") ?? "",
                                         BRLineItem = r1.Field<string>("Broker Items") ?? "",
                                         Action = r1.Field<string>("Action") ?? "",
                                         StandardLineItem = r1.Field<string>("Matched Items") ?? "",
                                         StandardValue =r2.Field<string>("LineItemDateValue") ?? ""
                                     }).ToList();

    • Marked as answer by Sudip_inn Thursday, February 20, 2020 3:39 PM
    Thursday, February 20, 2020 1:29 PM

All replies

  • Here is a good post on this which shows several possibilities.

    https://stackoverflow.com/questions/9685289/using-left-join-and-inner-join-in-the-same-query


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, February 19, 2020 2:45 PM
    Moderator
  • @Karen You probably do not understand my question.  In my post i have posted a linq join between 3 datatable where i used inner join between 3 datatables  but now i need to use left join and inner join in same above query. Due to my lack of knowledge i could not do it by linq in my above query.  So please compose a sample linq query where u will use left and inner join between  three datatables in same query by LINQ.

    Thanks


    • Edited by Sudip_inn Wednesday, February 19, 2020 8:01 PM
    Wednesday, February 19, 2020 7:52 PM
  • @Karen You probably do not understand my question.  In my post i have posted a linq join between 3 datatable where i used inner join between 3 datatables  but now i need to use left join and inner join in same above query. Due to my lack of knowledge i could not do it by linq in my above query.  So please compose a sample linq query where u will use left and inner join between  three datatables in same query by LINQ.

    Thanks


    Sorry, my mistake, I don't have time to rig up an example but would instead point you to the following article which at the bottom has a GitHub repository for the source code.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, February 19, 2020 10:31 PM
    Moderator
  • Hi Sudip_inn,
    Based on your description, I use left join and inner join to write a simple code example.

    select r1.id, r1.name, r2.date from dtMappedSectionNotEmpty as r1
        left join dtData as r2
            inner join _Periods as r3
            on r2.count= r3.sum
        on r1.id =r2.ID
        where r1.id=2;

    Hope it is helpful for you.
    Best Regards,
    Daniel Zhang


    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.

    Thursday, February 20, 2020 9:12 AM
  • Refer following two article in linq to use inner join and left join in c#.

    https://www.tutlane.com/tutorial/linq/linq-inner-join

    https://www.tutlane.com/tutorial/linq/linq-left-outer-join

    Thursday, February 20, 2020 11:06 AM
  • var query = (from r1 in dtMappedSectionNotEmpty.AsEnumerable()
                                     join r2 in dtData.AsEnumerable()
                                     on r1.Field<string>("Row").Trim().ToUpper() equals r2.Field<string>("RowCoordinate").Trim().ToUpper() into gj
    				 from subset in gj.DefaultIfEmpty()
                                    join r3 in _Periods.AsEnumerable()
                                     on r2.Field<string>("StandardDate").Replace("A", string.Empty).Replace("E", string.Empty).Trim().ToUpper() equals r3.Replace("A", string.Empty).Replace("E", string.Empty).Trim().ToUpper()
                                     where r1.Field<string>("Tab").Trim().ToUpper() == strTab.Trim().ToUpper()
                                     select new BrokerData
                                     {
                                         RowNumber = r1.Field<string>("Row") ?? "0",
                                         TabName = r1.Field<string>("Matched Section") ?? "",
                                         StandardDate = r3.ToString(),
                                         BRTab = r1.Field<string>("Tab") ?? "",
                                         BRLineItem = r1.Field<string>("Broker Items") ?? "",
                                         Action = r1.Field<string>("Action") ?? "",
                                         StandardLineItem = r1.Field<string>("Matched Items") ?? "",
                                         StandardValue =r2.Field<string>("LineItemDateValue") ?? ""
                                     }).ToList();

    • Marked as answer by Sudip_inn Thursday, February 20, 2020 3:39 PM
    Thursday, February 20, 2020 1:29 PM
  • hey i have posted that i am looking for left & inner join BY LINQ query......why you have given SQL. i want LINQ example where left & inner join will be in same query.
    Thursday, February 20, 2020 3:37 PM
  • i know how to form left & inner join by LINQ but when i have to use both left & inner join in same query then i am not being able to figure out the syntax. can u come with any LINQ example where left & inner will be used in same query.
    Thursday, February 20, 2020 3:39 PM
  • will check & let u know does it work or not. thanks
    Thursday, February 20, 2020 3:40 PM
  • best answer. thanks
    Saturday, May 23, 2020 9:10 AM