Hi...I'm trying to execute a left join between 2 datatables that will return all records from the left table without a corresponding value in the right table on the join criteria. As of now I have the following which returns nothing:
Dim Query1 = From exasset In dtExistingAssets _ GroupJoin asset In dtNewAssets _ On exasset("ACCOUNT_NAME") Equals asset("ACCOUNT_NAME") _ Into results = Group _ From f In results.DefaultIfEmpty _ Where IsDBNull(f) _ SelectNewWith _
{ ...................
I've seen several references to using 'Any' but I wasn't able get the syntax correct. Can anyone please help out? This is something that is really simple to accomplish in SQL but seems a lot more complicated in LINQ...thanks.
I would love to get a proper left join linq tutorial because while I generally have no problem with any of the other Linq construct, left joins always bog me down. Just today in a fairly large app that was up to then completely using Linq-Entities, I had to resort to an old style datareader for this one query. I meddled an hour or so before finally copy-pasting the T-SQL I had previously written in SQLMS over my whole Linq query in frustration. Why oh why did MS not include a LeftJoin in Linq is beyond me.
For the record, here is the SQL query in question. If someone manages to convert that to working Linq-Entities, props to you... (I'm still a bit bitter about the whole ordeal... I read many many tutorials, and I understand the concept, but something is fundamentally different between the T-SQL and Linq definitions of LEFT JOIN that I am missing...)
SELECT TOP 1 tc.testcode, tc.[timeout], pe.editioncode, d.dbmscode
FROM TestEditions te
inner join TestConfig tc on te.extestid = tc.id
inner join ProductEditions pe on te.exeditionid = pe.id
inner join dbms d on te.exdbmsid = d.id
inner join languages l on te.exlangid = l.id
inner join products p on pe.exproductid = p.id
-- This is the part that I can't get right in Linq...
left join testlog tl on tc.testcode = tl.testcode
and pe.editioncode = tl.editioncode
and l.langcode = tl.langcode
and d.dbmscode = tl.dbmscode
and p.productname = tl.productname
-- Specifically this. Linq refuses to
-- compare to an absolute value in the
-- ON clause.
and tl.exbuildid = 8466
-----------------------------------------------------
Where (tl.Success = 0 or tl.success is null)
and (tl.runningnow = 0 or tl.success is null)
and tc.inactive = 0
and p.productname = 'DYNACOM'
and l.langcode = 'EN'
order by tc.[timeout] asc, tc.testcode asc
And my best guess in Linq:
Dim dc As New AutoTestsEntities
Dim qNextTest = (From te In dc.TestEditions
Join tc In dc.TestConfig On te.ExTestID Equals tc.ID
Join pe In dc.ProductEditions On te.ExEditionID Equals pe.ID
Join d In dc.DBMS On te.exDbmsId Equals d.ID
Join l In dc.Languages On te.ExLangId Equals l.ID
Join p In dc.Products On pe.ExProductID Equals p.ID
Group Join _tl In dc.TestLog On tc.TestCode Equals _tl.TestCode _
And pe.EditionCode Equals _tl.EditionCode _
And l.LangCode Equals _tl.LangCode _
And d.DbmsCode Equals _tl.DbmsCode _
And p.ProductName Equals _tl.ProductName _
And _tl.ExBuildID Equals 8466 _
Into tlg = Group
From tl In tlg.DefaultIfEmpty()
Where (tl.Success = False Or tl.Success Is Nothing) _
And (tl.RunningNow = False Or tl.RunningNow Is Nothing) _
And tc.Inactive = False _
And p.ProductName.ToUpper = "DYNACOM" _
And l.LangCode.ToUpper = "EN"
Order By tc.Timeout Ascending, tc.TestCode Ascending
Select New TCTest With {.TestCode = tc.TestCode,
.Timeout = tc.Timeout,
.EditionCode = pe.EditionCode,
.DbmsCode = d.DbmsCode}).FirstOrDefault
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.