Can I join a table to a list using linq? [duplicate]

Question

I have a table as follows:

PersonalDetails  Columns are:  Name   BankName BranchName AccountNo Address 

I have another list that contains 'Name' and 'AccountNo'. I have to find all the records from table that whose respective 'Name' and 'AccountNo' are present in given list.

Any suggestion will be helpful.

I have done following but not of much use:

var duplicationhecklist = dataAccessdup.MST_FarmerProfile                           .Join(lstFarmerProfiles,                                  t => new { t.Name,t.AccountNo},                                  t1 => new { t1.Name, t1.AccountNo},                                  (t, t1) => new { t, t1 })                            .Select(x => new {                                                x.t1.Name,                                                x.t1.BankName,                                                x.t1.BranchName,                                                x.t1.AccountNo                                              }).ToList(); 

where lstFarmerProfiles is a list.

Answer 1

You probably found out that you can't join an Entity Framework LINQ query with a local list of entity objects, because it can't be translated into SQL. I would preselect the database data on the account numbers only and then join in memory.

var accountNumbers = lstFarmerProfiles.Select(x => x.AccountNo).ToArray();  var duplicationChecklist =          from profile in dataAccessdup.MST_FarmerProfile                                      .Where(p => accountNumbers                                                     .Contains(p.AccountNo))                                      .AsEnumerable() // Continue in memory         join param in lstFarmerProfiles on              new { profile.Name, profile.AccountNo} equals              new { param.Name, param.AccountNo}         select profile 

So you will never pull the bulk data into memory but the smallest selection you can probably get to proceed with.

If accountNumbers contains thousands of items, you may consider using a better scalable chunky Contains method.

Answer 2

Since you have the lists in .net of values you want to find, try to use the Contains method, for sample:

List<string> names = /* list of names */; List<string> accounts = /* list of account */;  var result = db.PersonalDetails.Where(x => names.Contains(x.Name) && accounts.Contains(x.AccountNo))                                .ToList();