LINQ for entity, wrong connection type

Question

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

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

Just before "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 the relationship between Inventory and ActiveList is not really 1-to-many, but Zero-to-many (I'm not sure what I use correct terms). In principle, an inventory item may or may not have a corresponding ActiveList.

If I changed this raw SQL to use LEFT OUTER JOIN , instead of INNER JOIN , SQL will return the values ​​I expect.

What is necessary to force the LEFT INTERNAL WORK?

I tried the recommended solution from Linq for entities - From one to many relationships - you need to leave the outer join instead of the 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 the connection / relationship is not built correctly? Any other ideas?

Thanks!

EDIT :: More Information

 -- 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), 

Answer 1

The main problem is that you have disabled referential integrity checks in your database.

Besides the obvious problem with bad data, this will not work with EF.

To date, the best option is to WS_ActiveList_ID to nullable, update the data to change all 0 to NULL and re-enable the restriction.

If you cannot do this, I think you will need to generate the SQL statement as a string and execute it using dbContext.Database.SqlQuery<T> ( MSDN )