Can I join a table to a list using 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.

I'd look into using table valued parameters and writing a custom SQL query for this... I can give more info if you're interested, but it doesn't really use linq, so not really an answer to your specific question. — John Gibb, Dec 10 '13 at 15:48

score 31 · Accepted Answer · edited May 23 '17 at 11:33

31

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.

edited May 23 '17 at 11:33

Community

1
1

answered Dec 10 '13 at 19:17

Gert Arnold

105,341
31
202
291

I gave some more details [here](http://stackoverflow.com/questions/26198860/entityframework-contains-query-of-composite-key/26201371#26201371). – Gert Arnold Oct 05 '14 at 21:43
just a theoretical question, but why can't a local list be translated into SQL? When the query actually runs, LINQ should be able to create a temp table or table variable with the data from the local list and then join on that. This is a feature that should absolutely be included in the framework. – jtate Jul 05 '18 at 13:07
@jtate There are some [private efforts](https://codereview.stackexchange.com/a/190364/7251) in that direction. To me these only show that it's not really trivial. – Gert Arnold Jul 05 '18 at 16:46
@GertArnold this is exactly what I was imagining. With fairly small in-memory collections, I think this would work great. – jtate Jul 10 '18 at 13:02

score 3 · Answer 2 · answered Dec 10 '13 at 14:16

3

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();

answered Dec 10 '13 at 14:16

Felipe Oriani

37,948
19
131
194

There is a single list that has names and accounts. – Saket Kumar Dec 10 '13 at 14:29
It wont match each record of database exactly to each row of list. – Saket Kumar Dec 11 '13 at 06:34

Magnus · Answer 3 · 2013-12-10T14:59:06.000

3

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();

edited Dec 10 '13 at 14:59

answered Dec 10 '13 at 14:45

Magnus

45,362
8
80
118

MST_FarmerProfile contains 2 million record. Will you still suggest the same? – Saket Kumar Dec 10 '13 at 14:52
@sake Perhaps you don't need to bring all the columns from the table into memory, do a select before the `.AsEnumerable()` to get the columns you need. The only way to know if the execution time is acceptable is to test it. – Magnus Dec 10 '13 at 14:57
@Saket How large is `lstFarmerProfiles` going to be? – Magnus Dec 10 '13 at 15:03
lstFarmerProfiles will contain 1000 records. – Saket Kumar Dec 10 '13 at 15:07
I get exception: "Object reference not set to an instance of an object." – Saket Kumar Dec 11 '13 at 06:43

score 2 · Answer 4 · answered Mar 23 '18 at 02:51

Since data is usually located on different machines or in separate processes at least: DB - is one and your in-memory list is your app, there is just 2 ways to do it.

Download as small data part from DB to local as possible and join locally (usually using AsEnumerable() or basically ToList()). You got many good thoughts on this in other answers.
Another one is different - upload your local data to server somehow and perform query on DB side. Uploading can be done differently: using some temp tables OR using VALUES. Fortunately there is a small extension for EF now (for both EF6 and EF Core) which you could try. It is EntityFrameworkCore.MemoryJoin (name might be confusing, but it supports both EF6 and EF Core). As stated in author's article it modifies SQL query passed to server and injects VALUES construction with data from your local list. And query is executed on DB server.

We just added this library and it is AWESOME!!!! This solves most situations where linq to entities stops you. (We were struggling with providing IDs and keeping the sort order) — TChadwick, Sep 14 '20 at 16:22

Bvrce · Answer 5 · 2013-12-10T15:47:45.707

If accountNo identifies the record then you could use:

var duplicationCheck = from farmerProfile in dataAccessdup.MST_FarmerProfile
                       join farmerFromList in lstFarmerProfiles
                       on farmerProfile.AccountNo equals farmerFromList.AccountNo
                       select new { 
                                      farmerProfile.Name, 
                                      farmerProfile.BankName, 
                                      farmerProfile.BranchName, 
                                      farmerProfile.AccountNo 
                                  };

If you need to join on name and account then this should work:

 var duplicationCheck = from farmerProfile in dataAccessdup.MST_FarmerProfile
                        join farmerFromList in lstFarmerProfiles
                        on new
                        {
                            accountNo = farmerProfile.AccountNo,
                            name = farmerProfile.Name
                        }
                        equals new
                        {
                            accountNo = farmerFromList.AccountNo,
                            name = farmerFromList.Name
                        }
                        select new
                        {
                            farmerProfile.Name,
                            farmerProfile.BankName,
                            farmerProfile.BranchName,
                            farmerProfile.AccountNo
                        };

If you are only going to go through duplicateChecklist once then leaving .ToList() out will be better for performance.

Can I join a table to a list using linq?

5 Answers5

Linked