Cannot compare elements of type 'System.Collections.Generic.ICollection`1 Only primitive types, enumeration types and entity types are supported

Question

I have written this code

IQueryable<Site> sites = context.MainTable.Include("RelatedTable");

if (!string.IsNullOrEmpty(param1)) {
    sites = sites.Where(s => s.RelatedTable != null && s.RelatedTable.Any(p => p.Name == param1.ToLower() && p.PolicyType == "primary"));
}

foreach (string secondaryPolicy in secondaryPolicies)
{
    sites = sites.Where(s => s.RelatedTable != null && s.RelatedTable.Any(p => p.Name == secondaryPolicy.ToLower() && p.PolicyType == "secondary"));
}

return sites.ToList();

However at the ToList line I am getting the exception

Cannot compare elements of type 'System.Collections.Generic.ICollection`1[[Project1, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]'. Only primitive types, enumeration types and entity types are supported.

Answer 1

You can't compare a related table to null directly. Instead, compare against your foreign key member (assuming that PrimaryTable reference RelatedTable using a member called RelatedTableId.

sites.Where(s => s.RelatedTableId != null && s.RelatedTable.Any(
    p => p.Name == param1.ToLower() && p.PolicyType == "primary"));

You may even be able to get away with removing the null check completely. Since this query is run against the database, you won't get a NullReferenceException and it may work. You'll have to double check on that though.

Answer 2

It is because that you have a null check in the where clause.

Answer 3

The error can occur if navigation collection compared with null. It should be checked if Any record exist. In the particular example Any is used anyway, so check collection to null is redundant

Incorrect

dbContext.MainTable.Where(c => c.RelatedTable==null )

Correct

dbContext.MainTable.Where(c => !c.RelatedTable.Any() )

Answer 4

Collection field can be null in this case you get exception NullReferenceException

when use RelatedTables.Any()

If you add RelatedTables != null as in a question then you can get

Cannot compare elements of type 'System.Collections.Generic.ICollection`1[[Project1, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]'. Only primitive types, enumeration types and entity types are supported.

If you get the NullReferenceException exception, lazy loading is not turned off and you are good with lazy loading for the field then to prevent exception mark field with virtual keyword to allow lazy loading for the field

virtual ICollection<Table> RelatedTables{ get; set; }

Answer 5

I don't have a Foreign Key field configured because the relationship between MainTable and RelatedTable in my case is 1 to 1. However for a 1 to many relationship if you don't have foreign key but you have a navigation property to the MainTable model in the RelatedModel model the following solution also works.

1 to 1

  var result = from s in context.Sites
               join r in context.RelatedTable on s.Id equals r.Id
               select s; 

  return result;

1 to many

  var result = from s in context.Sites
               join r in context.RelatedTable on s.Id equals r.Site.Id
               into rs
               where rs.RelatedTable.Any(p => p.Name == param1.ToLower() && p.PolicyType == "primary")
               select s

Answer 6

It work me , I just remove the null check;

correct: result=

db.EmpTable.FirstOrDefault().ProjectsAssign.Name,

InCorrect : result=

db.EmpTable!=null && db.EmpTable.FirstOrDefault().ProjectsAssign!=null ? 
      db.EmpTable.FirstOrDefault().ProjectsAssign.Name : null,