CODEWITHSUNDEEP
c#.netlinq
DanO
DanO

Reputation: 417

LINQ - I need to pull records from table 2 based on values in table 1

I have 2 tables, table 1 has a field which contains prefixes for companies.

The values retrieved from table 1 need to be used to get records from table 2 (based on the prefixes returned).

I coded below to do a startswith to demo what I need done, however what I need to do is build a dynamic starswith clause.

I know the code below wont compile, just putting it out there to show what I would like to accomplish.

//*   This pulls list of prefixes from Table 1

var xrfPrefixes = from xx in XrfPrefixes
 select new
 {
   xxx.CompanyPrefix
 };

//* Get list of prefixes
string PrefixList = "";
foreach (var xxx in xrfJobPrefix)
{
    PrefixList = PrefixList + xxx.FirmJobPrefix + ",";
}

//* Get back Matching records from table 2
var results = from p in Orders.Where(p => p.OrdCust.StartsWith(PrefixList))
select p;

Sample Data in Table 1 (Customers):

Sample data in table 2 (orders):

Based on the customer_prefixes in the Customers table, the records from Orders Table returned would be Rows 1,2,4 & 5

Upvotes: 1

Views: 317

Answers (2)

Heinzi
Heinzi

Reputation: 172448

  1. Create a PrefixList. Don't do it as a list of comma-separated strings, do it as a real List<String>. That means you have to split comma-separated values (using String.Split, for example):

    var prefixList = new List<string>();
foreach (var xxx in xrfJobPrefix) 
{ 
    prefixList.AddRange(xxx.FirmJobPrefix.Split(',').Select(pre => pre.Trim())); 
}

  2. After you have a PrefixList, you can do a simple LINQ query as follows:

    var results = from p in orders
              from pre in prefixList
              where p.OrdCust.StartsWith(pre)) 
              select p;

Upvotes: 1

Miguel Angelo
Miguel Angelo

Reputation: 24212

See if this works:

        var items = from o in Orders
                    where (
                        from pf in XrfPrefixes
                        where o.OrdCust.StartsWith(pf.CompanyPrefix)
                        select pf).Any()
                    select o;

Upvotes: 1

Related Questions