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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

Related Questions

Join two tables using LINQ to Entities, then group and total the columns from each tables.

First of all, you should setup navigation properties for relevant foreign keys (for example, 1:N relation of target to transactions, as well as from transaction to products). If setup correctly, the database design/EF schema should take most of the work from your hands. After that you can go:...(from target in targets where target.Year == year &&...
c# entity-framework linq linq-to-entities
asked by Femaref

Should I use lambda or LINQ for a multi-table join in Entity Framework 4.1?

I suggest you read this ...blog post... by CRAIG STUNTZ. The idea is that as long as you have correct relationship in your model, you can simply use navigation link to select all your properties without relying on JOIN operator. And honestly, this is the beauty of LINQ, why doing join yourself if your model can do it.
asp.net-mvc entity-framework lambda linq sql
asked by J.W.

Using Linq, get all items from list that are in another List<int>

Your code works but it will create the list of anonymous object, not string type...Instead of using ...(a => new { a.Title }..., you just use ...a => a.Title... if you just only want to get the title:...var myList = db.Items.Where(item => idsOnly.Contains(item.ID.Value)) .Select(a => a.Title).ToList();
c# entity-framework linq
asked by cuongle

LEFT JOIN or RIGHT JOIN using LINQ Entity-Framework

Trying joining using Linq. What should i use ? Left join or right join?... APPLICANT TABLE PROFILE TABLE APPLICANT_ID|profile_id|Applicant_Name| |profile_id|Applicant_Name 1 | NULL | RAY HEAVENS | | 1 | MARK LAPID 2 | NULL | BEN TULFO | | 2 | SUPER MAN 3 | 1...
asp.net asp.net-mvc c# entity-framework linq
asked by Enrique Gil

How to join two table from two different edmx using linq query

How to join two table from two different edmx using linq query.. Is there a way to query from 2 different edmx at a time....Thanks.
edmx entity-framework linq
asked by John Hawkins

How do I do a table join using LINQ and entity framework 6?

This is what i normally do when it comes to joining two or more tables, don't know if that's what you are looking for :...var info = from p in db.Exam join q in db.objective on p.objectiveid equals q.id join r in db.objectivedetails on q.objectivedeailsId equals r.id select new { ...
asp.net c# entity-framework linq sql
asked by Krunal Patil

How to use Linq to join entity tables with a cross-reference table

Let me preface this by saying I'm relatively new to Linq, but I seem to have grasped most of it pretty quickly. But this problem has me stumped. I've searched and searched to no avail....I am using code-first and have created have 2 models, "Items" and "Keywords". They have a many-to-many relationship on them. My entity models look like this:..
.net entity entity-framework entity-framework-6 linq
asked by Jason Thuli

asp.net mvc Join two tables to a single list item in html unordered list

<ul> @foreach (var teamre in dmodel.Teamre) { <li> @teamre.name </li> } ...I think your using the wrong collection to iterate through...see my code change above.
asp.net asp.net-mvc-5 entity-framework entity-framework-6 join
asked by bilpor

LINQ select from table where fieldValue is in another tables list of values

I suppose you are looking for all items whose particular field matches another field in your already avialable list . you could do something similar....dbContext.Table1.Where(p => listOfvalues.Any(l => p.Field == l.Field)).ToList(); ...EDIT Try using Contains... var values = listOfvalues.Select (x => x.Field ).ToList(); dbContext.Table1.Where(...
c# entity-framework-6 linq
asked by Ashley John

linq left join with filtering in joined

I am getting duplication meals here...IEnumerable<DTOHotMealsPrice> lst = (from m in this.dbEntity.HOT_MEALS join ml in this.dbEntity.HOT_MEALS_PRICE on m.MEALSID equals ml.MEALSID into mls from mls1 in mls.DefaultIfEmpty() where mls1.HOTID==hotelId select new DTOHotMealsPrice { MEALSID = m.MEALSID, MEALSNAME =...
c# entity-framework-6 linq
asked by safeena rasak

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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