I have a Linq statement like this:

IQueryable ActiveContract = (
    from c in context.Contracts
    join a in context.Abonents on c.AbonentID equals a.ID
    where a.ID != null && c.Hidden != true && c.Status == activeContractStatus)

It generates the following query:

SELECT 
    ...Contract properties...
    FROM [dbo].[Contracts] AS [Extent1]
    WHERE ([Extent1].[AbonentID] IS NOT NULL) AND (1 <> [Extent1].[Hidden]) AND ([Extent1].[Status] = @p__linq__0)

I've read this answer: Forcing linq to perform inner joins but in my case Contracts.AbonentID is nullable and unfortunately:

1. Every other property in Abonents is nullable except ID
2. It is completely normal (in business logic) to have an Abonent with all properties except Abonent.ID to be null (so I cannot add any not null check to the where clause for Abonents).
3. I need only the contracts that are connected to Abonents.
4. There are lots of cases when Contracts.AbonentID leads to nowhere (no Abonent with such ID).

Is there a way to generate something like:

SELECT 
    ...Contract properties...
    FROM [dbo].[Contracts] AS [Extent1]
    INNER JOIN [dbo].[Abonents] AS [Extent2] ON [Extent1].AbonentID = [Extent2].ID
    WHERE ([Extent2].[ID] IS NOT NULL) AND (1 <> [Extent1].[Hidden]) AND ([Extent1].[Status] = @p__linq__0)

forcing the check on Abonents existence?

For example, if I need to find all phone numbers of an active contract by the contract's number (unfortunately, not unique, as there can be lots of contracts in DB with the same number, but hidden, inactive by status or just orphaned (abonent with that id is missing)), I will use smth like this

List<long> phoneNumbers = (context.Contracts.ActiveContracts().Where(c => c.No == contractNoToFind).Phone.OrderByDescending(p => p.Date).Select(p => p.Number);

I want to state what "ActiveContract" is once, and use it throughout the entire DAL.

UPD 1: I forgot to mention that the LINQ query is a part of IQueryable, so in fact, I'm not selecting anything at that point. Also, that's why stored queries are also not the solution. Edited the question. Sorry for the inconvinience.

CodePudding user response：

Since you are using inner joins you don't need to check where a.ID != null since records where a.ID == null will be automaticaly ommited.

so you can make your where part

 where c.Hidden != true && c.Status == activeContractStatus

if you are still not happy with sql , just add this

 select c.No, a.Id).ToList().Select(i=>i.No);

I didn't see your classes, since you didn' t post them , but I guess this could be working too

var contNo = context.Contracts
     .Where(c=> c.AbonentId!= null &&  c.Hidden != true && c.Status == activeContractStatus)
    .Select(c=> c.No);

CodePudding user response：

Looks like EF6 assumes a foreign key exists in the database and simplifies the query on that assumption. You can explicitly check like this:

var contNo = (
      from c in db.Contracts
      where db.Abonents.Where(a => a.ID == c.AbonentID).Count() == 1
        && c.Hidden != true && c.Status == activeContractStatus
      select c.No).FirstOrDefault();

EF Core doesn't appear to do this, which is a better behavior as 1) you can have a Navigation Property without an enforced foreign key, and 2) SQL Server (at least) will simplify the query plan to eliminate the join if it only checks the existence of a related row and there is a "trusted" foreign key that enforces the existence of the row.