How to check related table with where clause iquerable

Question

I have a problem in my query of null variable when I try to use related table with include, my database is mysql

// working no error
var query = context.Category.Where(c => c != null && ListOfIds.Contains(c.Id)).ToList();
var count = context.Category.Include(x => x.Products).Count();

// error null
var query = context.Category.Include(x => x.Products).Where(c => c != null && ListOfIds.Contains(c.Id)).ToList();

Here My Model Product :

public class category {

public category() {}

public category(int productscount: this() {
    products = new List < Product > ();
    for (int i = 0; i < productscount: i++) {
        products.Add(new Product());
    }
}

public override int Id {
    get;
    set;
}

public List < Product > Products {
    get;
    set;
}}

Here my stack trace :

à MySql.Data.EntityFramework.SelectStatement.AddColumn(ColumnFragment column, Scope scope) à MySql.Data.EntityFramework.SelectGenerator.Visit(DbApplyExpression expression) à MySql.Data.EntityFramework.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) à MySql.Data.EntityFramework.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) à MySql.Data.EntityFramework.SelectGenerator.Visit(DbProjectExpression expression) à MySql.Data.EntityFramework.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) à MySql.Data.EntityFramework.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) à MySql.Data.EntityFramework.SelectGenerator.Visit(DbSortExpression expression) à MySql.Data.EntityFramework.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) à MySql.Data.EntityFramework.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) à MySql.Data.EntityFramework.SelectGenerator.Visit(DbProjectExpression expression) à MySql.Data.EntityFramework.SelectGenerator.GenerateSQL(DbCommandTree tree) à MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) à System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory) à System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext) à System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree) à System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable1 compiledQueryParameters, AliasGenerator aliasGenerator) à System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable1 forMergeOption) à System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass41_0.b__1() à System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) à System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass41_0.b__0() à System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) à System.Data.Entity.Core.Objects.ObjectQuery1..GetEnumerator>b__31_0() à System.Data.Entity.Internal.LazyEnumerator1.MoveNext() à System.Collections.Generic.List1..ctor(IEnumerable1 collection) à System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)

Answer 1

Simply adding an Include() statement in a query should not cause an error executing that query to materialize results. That isn't to say that if you are later iterating through those top level entities that the included relationship will not be empty.

If you only want to return Categories that are in that list of IDs and have at least one Product associated with them, eager loading those products:

var query = context.Category
    .Include(x => x.Products)
    .Where(c => c.Products.Any() 
        && ListOfIds.Contains(c.Id))
    .ToList();

Firstly, adding a Where clause for c => c != null is pretty much pointless. However, if you want categories that have Products then use c.Products.Any() to put a condition to check for those.

One recommendation I have to avoid Null references when dealing with the possibility that you will have entities with no related data is to ensure you initialize related collection references.

Instead of:

public class Category
{
    // ...
    public virtual ICollection<Product> Products { get; set; }
}

use

public class Category
{
    // ...
    public virtual ICollection<Product> Products { get; set; } = new List<Product>();
}

This ensures that any code that might access the products collection won't raise the null reference exception. Ideally you would support lazy loading as a failsafe in the event that the collection wasn't eager loaded. For new top-level entities (Category) this also means that your products collection is ready to go to add references to products.

Edit: Check that your relationship between category and product is mapped correctly, either via attributes, OnModelCreating, or an EntityTypeConfiguration, or whether you are relying on an EDMX for DB-First. EF can work out relationships by convention, but that can be leaving things to chance as to whether EF worked it out correctly.

For example, using attributes:

public int? CategoryId { get; set; }
[ForeignKey("CategoryId"), InverseProperty("Products")]
public Category Category{ get; set; }

This tells EF what the FK property in this entity is, and that this is related to the Products collection in the Category entity. Alternatively the [InverseProperty] could be put on the Products collection within Category. If EF isn't associating the Products collection properly with the relationship to a product's Category then there may be issues. Generally though this would result in a mapping error rather than something like NullReferenceException.

Another consideration might be an issue specific to the MySQL EF provider you are using. I am not aware of any MySQL issues around Include interfering with a query execution.

the last consideration might be if your code base is overriding EF's query execution for some reason to inspect and attempt to inject SQL or such. A clue might be in the Exception's call stack to see where this NullReferenceException is being raised, whether internal to EF or a method within the DbContext itself.

Edit 2: Ok, from your comments Ok, t sounds like you have one query with filtering where you end up getting the IDs, but then want to feed those IDs into this method to get the data and related records. This will result in a few problems, possibly limitations in the MySQL provider. Normally when you use something like Contains in a query, the provider will generally be trying to produce something like an IN() clause and ty in the JOIN for the related table. I'd be surprised if it worked with such a large set without the Include.

The first thing I'd check as a test is just trying loading all Categories with their products eager loaded...

var categories = context.Category.Include(x => x.Products).ToList();

If this causes an error then it might be a case where your mapping of Products or the relationship between product and category is configured to expect something like a required non-nullable value/reference and declared as a non-nullable member in the entity, but the data supports and has #null in that column on some rows. When loading the first few thousand rows, everything is valid, but it happens that some of the later data has an issue.

The next point is to not use that Contains approach for dealing with such a large set of IDs. From what you describe I'm guessing you have something like:

List<int> listOfIds = SomeMethodThatQueriesAndGetsIds(criteria);
var categories = GetCategoriesWithProducts(listOfIds);

To fix this where one method has a Query that has all of the filtering criteria and you don't want to duplicate that code, break off the query generation into a private method that returns IQueryable<Category> that takes the criteria and can be called by both the original method to Select IDs or whatever data that method needs, where your new method can call it, Include Products and ToList the results passing the same criteria instead of a list of 10,000 - 41,000 Ids.

For instance if the original method had something like:

var results = context.Category
    .Where(x => x.Condition == criteria.Condition
         && x.SomeOtherCondition == criteria.Condition2)
    .Select(x => new SomeDTO
    {
        CategoryId = x.Id,
        // ....
    }).ToList();
return results;

and you were generating your ListOfIds using the results.Select(x => x.CategoryId).ToList(); or such to feed to the new method... Change this in the original method to do something like this:

var results = getCategorysByCriteria(criteria)
    .Select(x => new SomeDTO
    {
        CategoryId = x.Id,
        // ....
    }).ToList();
return results;

then

private IQueryable<Category> getCategoriesByCriteria(Criteria criteria)
{
    return context.Category
    .Where(x => x.Condition == criteria.Condition
         && x.SomeOtherCondition == criteria.Condition2);
}

This way, in your new method, instead of passing the list of IDs, pass the criteria that would have been sent to the first method to get those IDs:

 var data = getCategoriesByCriteria(criteria)
     .Include(x => x.Products)
     .ToList();

The "Criteria" could be an object containing the criteria, or simply replaced with the parameters used for the method you wrote to get the IDs. The point is that the logic to build the Query is centralized in one spot and the methods using it can still customize how they execute and consume the query results.

Loading 10,000 or 41,000 entities and related data is certainly not a good idea. You should take a close look at why you want to load this data and either summarize or paginate these results. Users typically will not need 40k results all in one go, and even things like batch updates should probably use a more direct mechanism to update the data rather than EF entities. (I.e. a stored procedure, or a background process that will deal with smaller batches at a time and handle rollback capabilities manually if necessary.)