CODEWITHSUNDEEP
asp.net-mvclinqentity-framework
reidLinden
reidLinden

Reputation: 4160

LINQ to entity, wrong join type

I have a query that looks like so....

var q = Dal.TBLINVENTORies.Where(i => i.SHOWIT);
q = q.Where(i => i.dtStart < DateTime.Now || i.dtStart == null);
q = q.Where(i => i.dtEnd > DateTime.Now || i.dtEnd == null);
q = q.Where(i => i.sSystem.Contains("OE"));
q = q.Where(i => i.WS_ActiveList_ID == 0 || i.tblWS_ActiveList.WS_MasterList_ID == 16);
var test2 = q.ToList();

Immediately before the "ToList()", if I examine the query, I get the following sql (more or less)

SELECT [Extent1].*
FROM  [dbo].[TBLINVENTORY] AS [Extent1]
INNER JOIN [dbo].[tblWS_ActiveList] AS [Extent2] ON [Extent1].[WS_ActiveList_ID] = [Extent2].[ID]
WHERE ([Extent1].[SHOWIT] = 1) 
AND (([Extent1].[dtStart] <  CAST( SysDateTime() AS datetime2)) OR ([Extent1].[dtStart] IS NULL)) 
AND (([Extent1].[dtEnd] >  CAST( SysDateTime() AS datetime2)) OR ([Extent1].[dtEnd] IS NULL)) 
AND ([Extent1].[sSystem] LIKE '%OE%') 
AND ([Extent1].[WS_ActiveList_ID] = 0  OR [Extent2].[WS_MasterList_ID] = 16)

Unfortunately, this is not what I need, because relationship between "Inventory" and "ActiveList" is not really 1-to-Many, but Zero-to-Many (I'm not sure I'm using the correct terms). Basically, An inventory item might or might not have a related "ActiveList".

If I change that raw SQL to use a LEFT OUTER JOIN, instead of an INNER JOIN, the SQL returns the values I expect.

What is needed to force the LEFT OUTER JOIN?

I've tried the recommended solution from Linq to entities - One to many relationship - need left outer join instead of cross join , but,

var q2 = from inv in Dal.TBLINVENTORies from al in inv.tblWS_ActiveList

returns an error:

Error 65 An expression of type 'xxxx.DAL.tblWS_ActiveList' is not allowed in a subsequent from clause in a query expression with source type 'System.Data.Entity.DbSet<xxxx.DAL.TBLINVENTORY>'. Type inference failed in the call to 'SelectMany'.

I wonder if my link/relationship is constructed incorrectly? Any other ideas?

Thanks!

EDIT :: Additional Data

-- create foreign key, but don't enforce on existing values
ALTER TABLE [dbo].[tblInventory]  --the ONE Table
    WITH NOCHECK 
    ADD CONSTRAINT [FK__tblInventory.WS_ActiveList_ID__tblWS_ActiveList.ID] 
        FOREIGN KEY([WS_ActiveList_ID]) 
            REFERENCES [dbo].[tblWS_ActiveList] ([ID])    --the MANY Table
        NOT FOR REPLICATION
GO

-- disable enforcement of the foreign key, but leave it in place (virtual key)
ALTER TABLE [dbo].[tblInventory]  
    NOCHECK CONSTRAINT [FK__tblInventory.WS_ActiveList_ID__tblWS_ActiveList.ID] 
GO

and the definition of WS_ActiveList_ID:

[WS_ActiveList_ID] [int] NOT NULL CONSTRAINT [DF_TBLINVENTORY_WS_ActiveList_ID] DEFAULT (0),

Upvotes: 4

Views: 333

Answers (1)

Nick Butler
Nick Butler

Reputation: 24383

Your main problem is that you've turned off the referential integrity checks in your database.

Apart from the obvious problem of bad data, this won't work with EF.

By far the best option is to make WS_ActiveList_ID nullable, update your data to change all the 0s to NULLs and turn the constraint back on.

If you can't do that, I think you'll have to generate a SQL statement as a string and execute it with dbContext.Database.SqlQuery<T> ( MSDN )

Upvotes: 2

Related Questions