Getting error when I try to fetch rows using entity EF6

Question

I use linq to entity 6 code first.

Here is two entities class:

    public class Site 
    {
        public int Id { get; set; }
        public int UID { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public int? ContractId { get; set; }
        public int? SiteTypeId { get; set; }

        public virtual ICollection<SiteRegion> Regions { get; set; }
    }


    public class SiteRegion 
    {
        public int Id { get; set; }
        public int UID { get; set; }
        public int? SiteId { get; set; }
        public string Name { get; set; }
        public int? RegionTypeId { get; set; }

        [ForeignKey("SiteId")]
        public virtual Site Site { get; set; }

    }

As you can see above Regions field is constraint and I have one-to-many relation between to tables.

I have created this LINQ query to fetch desired rows from Sites table:

int?[] ContractId = [1,2];
int?[] siteTypeId = [1,2,3]; 

 var result = (from sites in context.Set<Site>()
               where contractsIDList.Contains(sites.ContractId) && 
               siteTypeId.Contains(sites.SiteTypeId) &&
               select sites).AsNoTracking<Site>();

And It works fine.

Now I have new requirement and I need to filter my query also by RegionTypeId column in SiteRegion table here is my new query:

int?[] ContractId = [1,2];
int?[] siteTypeId = [1,2,3]; 
int?[] regionTypeId = [1,2,3];

 var result = (from sites in context.Set<Site>()
               where contractsIDList.Contains(sites.ContractId) && 
               siteTypeId.Contains(sites.SiteTypeId) &&
    regionTypeId.Contains(sites.Regions.SelectMany(x=>x.RegionTypeId).ToArray())

               select sites).AsNoTracking<Site>();

But I get error:

Error   36  'int?[]' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.Queryable.Contains<TSource>(System.Linq.IQueryable<TSource>, TSource)' has some invalid arguments 

On this row:

regionTypeId.Contains(sites.Regions.SelectMany(x=>x.RegionTypeId).ToArray())

How to fix my query above to get the desired rows?

Answer 1

You need to do it as shown below.

Note :

Wrong : regionTypeId.Contains(sites.Regions.SelectMany(x=>x.RegionTypeId).ToArray())

Correct : regionTypeId.Any(item => sites.Regions.Select(x => x.RegionTypeId).Contains(item))

Working sample :

 int?[] contractsIDList = { 1, 2};
 int?[] siteTypeId = { 1, 2, 3};
 int?[] regionTypeId = { 1, 2, 3};

var result = (from sites in db.Set<Site>()
              where contractsIDList.Contains(sites.ContractId) && siteTypeId.Contains(sites.SiteTypeId)
              && regionTypeId.Any(item => sites.Regions.Select(x => x.RegionTypeId).Contains(item))
              select sites).AsNoTracking<Site>();

Result :