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
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 }withselect cl.CUSTNMBRwhich 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.ProvnumI 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.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!