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

Article
2020-02-19

Question

_{Wednesday, February 19, 2020 2:15 PM}

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

All replies (10)

_{Thursday, February 20, 2020 1:29 PM ✅Answered}

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();

_{Wednesday, February 19, 2020 2:45 PM}

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

_{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

_{Wednesday, February 19, 2020 10:31 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.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

_{Thursday, February 20, 2020 9:12 AM}

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 [email protected].

_{Thursday, February 20, 2020 11:06 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 3:37 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:39 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:40 PM}

will check & let u know does it work or not. thanks

_{Saturday, May 23, 2020 9:10 AM}

best answer. thanks

Share via

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

Question

All replies (10)

Additional resources