[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

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


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;
        }

Open in new window

0
sbornstein2
Asked:
sbornstein2
  • 3
  • 2
1 Solution
 
MikeTooleCommented:
Can a customer appear in more than one group? If so, the Join will return duplicates - one per group.
0
 
sbornstein2Author Commented:
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?
0
 
sbornstein2Author Commented:
Sorry actually I cant use distinct because I need the whole customer records "select CUST" TOLIST.  
0
 
MikeTooleCommented:
You need to Group by customer.
The attached code is a trivial example.
It joins in the Where clause rather than explicitly using the Join statement, but that makes no difference to the logic.
L1 is populated with 5 rows, as you would expect from a Join.
L2 gets just 2 rows

        Dim cust = {1, 2, 3, 4}
        Dim cGroup = {2, 2, 4, 4, 4}
        Dim L1 = From c In cust, g In cGroup Where c = g Select c
        Dim L2 = From c In cust, g In cGroup Where c = g Group c By c Into Group

Open in new window

0
 
sbornstein2Author Commented:
thank you.  this was great, sorry it took me a while to award this.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now