I am having 2 related Entity Framework Models one to many relation exists between the tables

Contact

Id 
Name
....

public ICollection<ContactAddress> Addresses { get; set; }

ContactAddress

[Key]
public int Id { get; set; }

[Required, MaxLength(150)]
public string Line1 { get; set; }

[ForeignKey(nameof(Contact))]
public int ContactId { get; set; }

public Contact Contact { get; set; } 

[ForeignKey(nameof(Country))]
public int? CountryId { get; set; }

public Country Country { get; set; }

public bool IsPrimary { get; set; }

The ContactAddress Table the CountryId is not a required field It can be null in some cases if the country of contact is not known

I am writing a filtering query to retrieve the results of contacts from a specific country ids and Filtering query is like below

IQueryable<Contact> query = context.Contacts;

    if (searchModel.SelectedCountries?.Count() > 0)
    {
        List<int> country_ids = searchModel.SelectedCountries.Select(x => x.Id).ToList(); //Prepare a list of country ids filtering needs to be applied
        query = query.Include(c => c.Addresses.Where(a => country_ids.Contains((int)a.CountryId)  && a.IsPrimary == true )  );
    }
    
    int totalResultsCount = await query.CountAsync().ConfigureAwait(false);

The generated sql is like

 SELECT COUNT(*)  FROM `Contacts` AS `c`
LEFT JOIN ( SELECT * FROM `ContactAddresses` AS `c0`
    WHERE `c0`.`CountryId` = 114 AND  `c0`.`IsPrimary`
) AS `t` ON `c`.`Id` = `t`.`ContactId`
ORDER BY `c`.`Id`, `t`.`Id`  

which returns 497 recods from my database table and this is equal to the total no of contacts in my table and i can see that the country filtering is not applied with my EF query. As soon as i changed LEFT JOIN to INNER JOIN in the query manually , i am getting 25 records which is correct for the criterias

' CountryId = 114 IsPrimary =1 '

So how can i enforce the INNER JOIN instead of LEFT JOIN on my IQueryable filtering criteria's ? Or is there any way to return the results accurately with EF query?

CodePudding user response：

If you are just trying to get contacts whose addresses match at least one of the selected filtering countries, then your query should be simply

    List<int> country_ids = searchModel.SelectedCountries.Select(x => x.Id).ToList(); //Prepare a list of country ids filtering needs to be applied
    var query = await context.Contacts.Where(c => c.Addresses.Any(a => a.IsPrimary && country_ids.Contains(a.CountryId)).ToListAsync(); 

Any joins will be implicitly generated by EF behind the scenes. There's no need for any Include() calls either.

If that Contains() call generates a compiler error, you might be able to replace it with Contains(a.CountryId ?? 0) though I strongly doubt that will be the case or the alternate will actually compile.

CodePudding user response：

You can use LINQ join, here's an example of query syntax.

IQueryable<Contact> query = context.Contacts;

if (searchModel.SelectedCountries?.Count() > 0)
{
    var country_ids = searchModel.SelectedCountries.Select(x => x.Id).ToList();

    query = from q in query
            join a in context.ContactAddresses on q.Id equals a.ContactId
            where a.CountryId.HasValue && country_ids.Contains((a.CountryId.Value) && a.IsPrimary
            select q;
}

int totalResultsCount = await query.CountAsync().ConfigureAwait(false);

CodePudding user response：

Your query should look something like the following pseudo code:

List<int> country_ids = searchModel.SelectedCountries.Select(x => x.Id).ToList(); //Prepare a list of country ids filtering needs to be applied

  var query =  context.Contacts.Join(context.Addresses,
        contact => contact.Id,
        address => address.ContactId,
        (contact,address) => new { Contact, Address })
       .Where(_ => country_ids.Contains((int)_.Address.CountryId)  && x.Address.IsPrimary == true);