Entity Framework LINQ joining 5 tables on multiple columns (impossible?!)

asp.net-mvc asp.net-mvc-4 entity-framework join linq-to-entities

Question

Please see image: enter image description here

I am trying to create a LINQ query that combines 5 different tables on 1 to 3 different columns. I am basically taking the examples of 2 tables and repeating the join sections for tables 3 through 5.

UPDATE: how do I get the following LINQ query to work?????

    var match = (from t1 in context.cKNA1
                 join t2 in context.cKNB1 on 
                        new { t1.KUNNR, t1.RowId } equals 
                        new { t2.KUNNR, t2.RowId }
                 join t3 in context.cKNVV on 
                        new { t2.KUNNR, t2.RowId } equals 
                        new { t3.KUNNR, t3.RowId }
                 join t4 in context.cKNVH on 
                        new { t3.KUNNR, t3.RowId } equals 
                        new { t4.KUNNR, t4.RowId }
        SELECT ....columns here....)

I can't find anything online where there are more than 3 tables *AND 2 or more columns.

1
2
8/13/2013 5:31:54 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

Write this query:

 var match = from t1 in context.cKNA1
             join t2 in context.cKNB1 on 
                    new { t1.KUNNR, t1.RowId } equals 
                    new { t2.KUNNR, t2.RowId }
             join t3 in context.cKNVV on 
                    new { t2.KUNNR, t2.RowId } equals 
                    new { t3.KUNNR, t3.RowId }
             join t4 in context.cKNVH on 
                    new { t3.KUNNR, t3.RowId } equals 
                    new { t4.KUNNR, t4.RowId }
    SELECT t1.KUNNR;

In fact, just remove the parenthesis. If it still doesn't work, the visual studio will mark one of those join words as error. If so, it means that the columns you are trying to link, have different data types.

Suppose that the VS mark the first join word as error. So, it means that t1.KUNNR and t2.KUNNR have different data types, or t1.RowId and t2.RowId have different data types.

Check and correct your data types, the problem will go away.

3
7/19/2013 9:23:42 AM

Related Questions

Can Entity Framework handle several result sets from a stored method (each from connected tables)?

This isn't supported out of the box in the new EF....But there is a project on CodeGallery called ...EF Extensions... that shows how to do this easily enough....Hope this helps...Alex
entity-framework linq-to-entities linq-to-sql
asked by Alex James

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

Linq to Entity Join table with multiple OR conditions

You don't have to use the join syntax. Adding the predicates in a ...where... clause has the same effect and you can add more conditions:...var query = (from RR in context.TableOne from M in context.TableTwo where RR.OrderedProductId == M.ProductID || RR.SoldProductId == M.ProductID // Your join ...
c# entity-framework linq linq-to-entities
asked by Gert Arnold

LEFT JOIN or RIGHT JOIN using LINQ Entity-Framework

Updated for the error: ...You will need to create a new class with the required properties and return it.... var applicantList = (from app in context.APPLICANTs join a in context.Profiles on app.Profile_id equals a.PROFILE_ID into output from j in output.DefaultIfEmpty() ...
asp.net asp.net-mvc c# entity-framework linq
asked by cheedep

Join multiple table using generic repository patten with Entity framework with unit of work

If your generic repositories expose an IQueryable method, you should be able to use a LINQ join to query both repositories:...var items = from c in customerRepository.AsQueryable() join l in libraryRepository.AsQueryable() on c.SomeProperty equals l.SomeOtherProperty select new { CustomerName = c.FirstName, LibraryName = l.N...
entity-framework generics repository unit-of-work
asked by DanS

Entity Framework left join on multiple tables creates inner join instead

The ...INNER JOINS... represent required n:1 associations. If you had navigation properties, the join would be a reference to another, required, entity, not a collection. EF knows from the data model that an ...OUTER JOIN... would have no effect here, so it generates an ...INNER JOIN....
c# entity-framework entity-framework-6 linq
asked by Gert Arnold

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

I think what you looking for is a query like this:... var keywordIds = new List<int> {1, 3, 5, 7, 9}; var items = (from s in db.Items from c in s.Keywords where keywordIds.Contains(c.KeywordId) select s).Distinct(); ...If you want to bring all the selected items to memory, then call the ...ToList..
.net entity entity-framework entity-framework-6 linq
asked by octavioccl

Entity Framework left join multiple tables failure

@Steve Li...For you, to anwer your question: As in the second comment mentioned, read the MSDN reference for C# Linq about the ...join-clause.... You should try to avoid the ...Left Outer Join... - using the ...DefaultIfEmpty... Function....To make your code working, try this:...var result = from e1 in entities1 join e2 in entiti...
c# entity-framework-6 left-join
asked by Radinator

Get selected values after joining multiple tables in Entity Framework 6 using Lambda

I had later figured out the issue. I had the changed the configFormatter in my WebAPIconfig.cs file:... config.Formatters.Remove(config.Formatters.XmlFormatter); ...Once I removed it, the LINQ query worked as expected. I hope it may help someone else in future!
asp.net-web-api2 entity-framework-6 lambda linq sql
asked by sandiejat

Select multiple columns from table based on different set of multiple columns

I'm trying to get query like this from LINQ/EF6 in C#...SELECT "ID_column" FROM "Entity" WHERE ("ColumnA","ColumnB") IN (('Value_0_0','Value_0_1'),('Value_1_0','Value_1_1'), ...); ...But I'm getting the following error for the code bellow...Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are s...
.net c# entity-framework-6 linq postgresql
asked by user1833414

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