Converting Sql query with 2 "left Join" and multi conditions to entity framework query

Question

I tried to convert this sql query

Select * from ( select SenDa.*,Prod.ProductKHK,FCod.FailCodeDesc from databa.dbo.SensorData as SenDa left join Products as Prod on SenDa.ProductID = Prod.ProductID left join FailCodes as FCod on SenDa.FailCode = FCod.FailCode and (FCod.ProdLineID =0 or FCod.ProdLineID = FCod.ProdLineID) ) as SenDa

to sql Linq so :

var SensDatJoinFail = (from SensDat in Jas_en.SenDatas join Prod in Jas_en.Products on SensDat.ProductID equals Prod.ProductID join FCod in Jas_en.FailCodes on SensDat.FailCode equals FCod.FailCode1 where FCod.ProdLineID == 0 || FCod.ProdLineID == FCod.ProdLineID select new { Serial_No = SensDat.Serial_No, OrderID = SensDat.OrderID, Artikelnummer = Prod.ProductKHK, StartProcTime = SensDat.StartProcTime, EndProcTime = SensDat.EndProcTime, Packaged = SensDat.Packaged, Labeled = SensDat.Labeled, Reworked = SensDat.Reworked, LastStation = SensDat.LastStation, FailCode = SensDat.FailCode, FailCodeDesc = FCod.FailCodeDesc, });

but I get difference in the result of query in rows count

I don't know , where is the problem ?

David Browne - Microsoft · Accepted Answer · 2018-05-15 12:12:52Z

This is an EF FAQ. Don't use JOINS in LINQ to Entitites. Just navigate your Navigation Properties. Something like:

 from SensDat in Jas_en.SenDatas select new { Serial_No = SensDat.Serial_No, OrderID = SensDat.OrderID, Artikelnummer = SensDat.Product.ProductKHK, StartProcTime = SensDat.StartProcTime, EndProcTime = SensDat.EndProcTime, Packaged = SensDat.Packaged, Labeled = SensDat.Labeled, Reworked = SensDat.Reworked, LastStation = SensDat.LastStation, FailCode = SensDat.FailCode, FailCodeDesc = SensDat.FailCode.FailCodeDesc, });

Thanks Yes, I know: but the problem is that I can't change the database Structure and there is no relationships between the tables. Therefore I used join.
You don't need FKs in the database to have Navigation Properties in your EF model.

Pavel Kovalev · Accepted Answer · 2018-05-15 22:29:22Z

You are getting less rows because you are using inner join's instead left joins. Try something like this:

var result= from table1 in dbo.Table1 from table2 in dbo.Table2 .Where(i=>i.Id == table1.Id) .DefaultIfEmpty();

or you can take a look how to Perform left outer joins

 Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" }; Person terry = new Person { FirstName = "Terry", LastName = "Adams" }; Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" }; Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" }; Pet barley = new Pet { Name = "Barley", Owner = terry }; Pet boots = new Pet { Name = "Boots", Owner = terry }; Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte }; Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry }; Pet daisy = new Pet { Name = "Daisy", Owner = magnus }; // Create two lists. List<Person> people = new List<Person> { magnus, terry, charlotte, arlene }; List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy }; var query = from person in people join pet in pets on person equals pet.Owner into gj from subpet in gj.DefaultIfEmpty() select new { person.FirstName, PetName = subpet?.Name ?? String.Empty };

Collectives™ on Stack Overflow

Converting Sql query with 2 "left Join" and multi conditions to entity framework query

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related