Can I join a table to a list using linq?

c#-4.0 entity-framework linq

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.

1
15
12/10/2013 7:24:51 PM

Accepted Answer

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.

21
5/23/2017 11:33:24 AM

Popular Answer

If MST_FarmerProfile is not super large I think you best option is to bring it into memory using AsEnumerable() and do the joining there.

var duplicationhecklist = 
             (from x in dataAccessdup.MST_FarmerProfile
                        .Select(z => new {
                                            z.Name, 
                                            z.BankName, 
                                            z.BranchName,
                                            z.AccountNo
                                          }).AsEnumerable()
              join y in lstFarmerProfiles
                 on new { x.Name, x.AccountNo} equals new { y.Name, y.AccountNo}
              select x).ToList();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow