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.

Solution 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.

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

Solution 3

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.

1. 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.
2. 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.

Solution 4

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

View more solutions

Comments

[gleng]

How about you show us what you've attempted first?

[Saket Kumar]

@gleng: Now can u help?

[Bvrce]

Does AccountNo uniquely identity the record?

[John Gibb]

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.

[Saket Kumar]

There is a single list that has names and accounts.

[Saket Kumar]

MST_FarmerProfile contains 2 million record. Will you still suggest the same?

[Magnus]

@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]

@Saket How large is lstFarmerProfiles going to be?

[Saket Kumar]

lstFarmerProfiles will contain 1000 records.

[Magnus]

@Saket Let me know how it goes.

[Saket Kumar]

It wont match each record of database exactly to each row of list.

[Saket Kumar]

I get exception: "Object reference not set to an instance of an object."

[Gert Arnold]

I gave some more details here.

[jtate]

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.

[Gert Arnold]

@jtate There are some private efforts in that direction. To me these only show that it's not really trivial.

[jtate]

@GertArnold this is exactly what I was imagining. With fairly small in-memory collections, I think this would work great.

[TChadwick]

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)