Is there a more optimal/elegant way to implement sub queries?

This works the way I want, but seems a lot of duplication and probably takes way longer than it needs to:

/*QA */ var providerIds = new[] { "315276" ,"145479" ,"455731" }; /**/ // match providers with customers by phone number /////////////////////////////////////// var matches = ( from p in ProviderInfo from r in TM99R.RM00101s.Where( r => ( p.PHONE.Substring( 0 ,10 ) == r.PHONE1.Substring( 0 ,10 ) || p.PHONE.Substring( 0 ,10 ) == r.PHONE2.Substring( 0 ,10 ) || p.PHONE.Substring( 0 ,10 ) == r.PHONE3.Substring( 0 ,10 ) ) ) /* QA */ where providerIds.Contains( p.Provnum ) orderby p.Provnum /**/ select new { actualPhone = p.PHONE.Substring( 0 ,10 ) ,p.Provnum ,r.CUSTNMBR } ); // group results /////////////////////////////////////// var providers = ( from p in matches /* QA */ where providerIds.Contains( p.Provnum ) orderby p.Provnum /**/ group p by new{ p.Provnum, p.actualPhone } into g select new{ Provnum = g.Key.Provnum ,Phone = g.Key.actualPhone ,CustomerCount = g.Count() // compile multiple customer numbers in to one string ,CustomerList = ( from cl in matches where cl.actualPhone == g.Key.actualPhone orderby cl.CUSTNMBR.Length select new { cl.CUSTNMBR } ).ToList() // select shortest CUSTNMBR string ,PrimaryCustomerNumber = ( from cl in matches where cl.actualPhone == g.Key.actualPhone orderby cl.CUSTNMBR.Length select new { cl.CUSTNMBR } ).ToList().First() } );

Comments

  • You can use the let command to avoid duplication, eg
    var providers = ( from p in matches group p by new { p.Provnum, p.actualPhone } into g let CustomerList = ( from cl in matches where cl.actualPhone == g.Key.actualPhone orderby cl.CUSTNMBR.Length select cl.CUSTNMBR ).ToList() select new { Provnum = g.Key.Provnum ,Phone = g.Key.actualPhone ,CustomerCount = g.Count() , CustomerList ,PrimaryCustomerNumber = CustomerList.First() }

    I have also replaced your select new { cl.CUSTNMBR } with select cl.CUSTNMBR which isn't necessary, but is probably more efficient.
    You are already filtering and ordering matches so there is no need for the section where providerIds.Contains( p.Provnum ) orderby p.Provnum

    I don't what your data layout is, but it looks like you are grouping by Provnum and actualPhone, and then for each group you are selecting all matching phonenumbers ignoring the value Provnum. If this is deliberate, then that's ok, but if you are looking for the all records which match both phonenumbers and Provnum, then you can further simplify your code to CustomerList = g.Select(a=>a.CUSTNMBR).ToList(),

    Most of these would make your code smaller and maybe more readable, but I don't think it would have much effect with execution speed. With Linq2Sql you need to be careful to make sure the TSQL generated is efficient. (Of course, one of the benefits of LinqPad is that you can see easily see what TSQL is being generated).

    In this example, since matches is doing the filtering, you might find it more efficient to perform the grouping in memory, rather than asking SQL to do it. This can be done simply by adding .ToList() to the end of your matches declaration.
  • edited September 2017
    thanks @sgmoore, you rock! always appreciate your insights
    I'm all about clean code so like the revision a lot. To show my appreciation, I'll explain the background and result:

    Yes, I'm basically trying to join two datasets based on phone numbers. Provnum is the id on providers and customernumber is the id in customers. The additional complexity comes when dealing with multiple customer matches to one provider. Hence, I am selecting a primary customer based on the shortest customerNumber string length but storing the customerNumber results in a list for reference.

    The data structure is as follows:
    Provnum, phone, countOfCustomers, ListOfCustomers, PrimaryCustomer

    Thanks again!
  • edited October 2017
    Thanx a lot for this response
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!