How to write join query with multiple column - LINQ

Question

I have a situation where two tables should be joined with multiple columns with or condition. Here, I have a sample of sql query but i was not able to convert it into linq query.

select cm.* from Customer cm
inner join #temp tmp
on cm.CustomerCode = tmp.NewNLKNo or cm.OldAcNo = tmp.OldNLKNo

This is how i have write linq query

await (from cm in Context.CustomerMaster
                         join li in list.PortalCustomerDetailViewModel
                         on new { OldNLKNo = cm.OldAcNo, NewNLKNo = cm.CustomerCode } equals new { OldNLKNo = li.OldNLKNo, NewNLKNo = li.NewNLKNo }
                         select new CustomerInfoViewModel
                         {
                             CustomerId = cm.Id,
                             CustomerCode = cm.CustomerCode,
                             CustomerFullName = cm.CustomerFullName,
                             OldCustomerCode = cm.OldCustomerCode,
                             IsCorporateCustomer = cm.IsCorporateCustomer
                         }).ToListAsync();

But this query doesn't returns as expected. How do I convert this sql query into linq.

Thank you

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Sep 30 '20 at 17:29

score 0 · Answer 1 · answered Sep 30 '20 at 20:39

You didn't tell if list.PortalCustomerDetailViewModel is some information in the database, or in your local process. It seems that this is in your local process, your query will have to transfer it to the database (maybe that is why it is Tmp in your SQL?)

Requirement: give me all properties of a CustomerMaster for all CustomerMasters where exists at least one PortalCustomerDetailViewModel where

   customerMaster.CustomerCode == portalCustomerDetailViewModel.NewNLKNo
|| customerMaster.OldAcNo == portalCustomerDetailViewModel.OldNLKNo

You can't use a normal Join, because a Join works with an AND, you want to work with OR

What you could do, is Select all CustomerMasters where there is any PortalCustomerDetailViewModel that fulfills the provided OR:

I only transfer those properties of list.PortalCustomerDetailViewModel to the database that I need to use in the OR expression:

var checkProperties = list.PortalCustomerDetailViewModel
    .Select(portalCustomerDetail => new
    {
        NewNlkNo = portalCustomerDetail.NewNlkNo,
        OldNLKNo = portalCustomerDetail.OldNLKNo,
    });

var result = dbContext.CustomerMasters.Where(customerMaster =>
    checkProperties.Where(checkProperty => 
        customerMaster.CustomerCode == checkProperty.NewNLKNo
     || customerMaster.OldAcNo == checkProperty.OldNLKNo)).Any()))
.Select(customerMaster => new CustomerInfoViewModel
{
    Id = customerMaster.Id,
    Name = customerMaster.Name,
    ...
});

In words: from each portalCustomerDetail in list.PortalCustomerDetailViewModel, extract the properties NewNKLNo and OldNLKNo.

Then from the table of CustomerMasters, keep only those customerMasters that have at least one portalCustomerDetail with the properties as described in the OR statement.

From every remaining CustomerMasters, create one new CustomerInfoViewModel containing properties ...

score 0 · Answer 2 · answered Oct 01 '20 at 02:38

select cm.* from Customer cm
inner join #temp tmp
on cm.CustomerCode = tmp.NewNLKNo or cm.OldAcNo = tmp.OldNLKNo

You don't have to use the join syntax. Adding the predicates in a where clause could get the same result. Try to use the following code:

await (from cm in Context.CustomerMaster
       from li in list.PortalCustomerDetailViewModel
       where cm.CustomerCode == li.NewNLKNo || cm.OldAcNo = li.OldNLKNo
       select new CustomerInfoViewModel
             {
                CustomerId = cm.Id,
                CustomerCode = cm.CustomerCode,
                CustomerFullName = cm.CustomerFullName,
                OldCustomerCode = cm.OldCustomerCode,
                IsCorporateCustomer = cm.IsCorporateCustomer
              }).ToListAsync();

score 0 · Answer 3 · answered Oct 04 '20 at 16:03

var result=_db.Customer
.groupjoin(_db.#temp ,jc=>jc.CustomerCode,c=> c.NewNLKNo,(jc,c)=>{jc,c=c.firstordefault()})
.groupjoin(_db.#temp ,jc2=>jc2.OldAcNo,c2=> c2.OldNLKNo,(jc2,c2)=>{jc2,c2=c2.firstordefault()})
.select(x=> new{
//as you want
}).distinct().tolist();

How to write join query with multiple column - LINQ

3 Answers3