sbornstein2
asked on
LINQ Query - Left Join Question
Hello all,
From my attached LINQ query I have here, I am having trouble where I only want to LEFT JOIN the CUSTGRP table to the CUST table so I can use in my where condition a field from the CUSTGRP table as shown. How do I do this where I only return the DISTINCT CUST records but can filter out those based on the cust group filter I pass into it?
I pass two param strings as you can see which one of them I filter the CUST table and the second I need to filter the CUSTGRP table so only I only pull the Customer records that have that condition and I join the two tables on CUSTID. Again I only want the distinct records though from CUST only for the return results.
Hopefully this makes sense. I tried messing around with the DefaultIfEmpty() but was always getting duplicate rows from CUST.
Thanks
From my attached LINQ query I have here, I am having trouble where I only want to LEFT JOIN the CUSTGRP table to the CUST table so I can use in my where condition a field from the CUSTGRP table as shown. How do I do this where I only return the DISTINCT CUST records but can filter out those based on the cust group filter I pass into it?
I pass two param strings as you can see which one of them I filter the CUST table and the second I need to filter the CUSTGRP table so only I only pull the Customer records that have that condition and I join the two tables on CUSTID. Again I only want the distinct records though from CUST only for the return results.
Hopefully this makes sense. I tried messing around with the DefaultIfEmpty() but was always getting duplicate rows from CUST.
Thanks
public List<Customer> GetCustByName(string custRegionFragment, string grpFragment) {
List<Customer> cf = new List<Customer>();
using (CC = new CustModelEntities()) {
cf = (from CUST in CC.Customers
join CUSTGRP in CC.CustGroup on
CUST.CustId equals CUSTGRP.CustId
where CUST.CUSTLOC != null &&
(string.IsNullOrEmpty(custRegionFragment) ||
CUST.CUSTREGION.ToUpper().Contains(custRegionFragment.ToUpper())) &&
(string.IsNullOrEmpty(grpFragment) ||
CUSTGRP.GROUP.ToUpper().Contains(grpFragment.ToUpper()))
select CUST).ToList();
}
return cf;
}
Can a customer appear in more than one group? If so, the Join will return duplicates - one per group.
ASKER
A customer will have multiple groups. The user though is going to pass a fragment of a group name though such as 'GRPA" and there will be records that have GRPA1234 etc.
Customer ABC might belong to both GRPA1234 and another record GRPA5678. I only want to pull that customer once. Should I use a distinct or something?
Customer ABC might belong to both GRPA1234 and another record GRPA5678. I only want to pull that customer once. Should I use a distinct or something?
ASKER
Sorry actually I cant use distinct because I need the whole customer records "select CUST" TOLIST.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you. this was great, sorry it took me a while to award this.