CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworklinq
amofialka
amofialka

Reputation: 25

Filter query who contains ICollection property via LINQ

I have 2 models: ApplicationUser and TaskModel

public class ApplicationUser : IdentityUser
{
     public virtual ICollection<TaskModel> Tasks { get; set; }
     ...
}

and

public class TaskModel
{
     public virtual ICollection<ApplicationUser> Managers { get; set; }
     ...
}

I want select from database only that record who contains some AplicationUser object in Managers. Than I tried using LINQ

ApplicationUser currentUser = "SomeObject";
var taskModels = db.Tasks.Where(t => t.Managers.Contains(currentUser)).Include(t => t.Autor);

But that generated error in Where clause. The error message says "Unable to create a constant "TaskToDo.Models.ApplicationUser" type. In this context it supports only primitive types, and enumeration types." I get what I need via next code

var allTasks = db.Tasks.Include(t => t.Autor).ToList();
List<TaskModel> filterTasks = new List<TaskModel>();
foreach (var task in allTasks)
{
   if (task.Managers.Contains(currentUser))
       filterTasks.Add(task);
}
return View(filterTasks);

But I'd like to know how it solve via LINQ syntax

Upvotes: 2

Views: 1762

Answers (2)

fknx
fknx

Reputation: 1785

You can't use Contains with your ApplicationUser instance because the query is executed by LINQ to SQL (or LINQ to Entities if you are using the Entity Framework) and obviously your database does not know your ApplicationUser class.

Your second approach is working because after calling ToList() the remaining code is executed by LINQ to Objects. It is equal to the following statement:

db.Tasks.Include(t => t.Autor).ToList().Where(t => t.Managers.Contains(currentUser)).ToList();

Note the additional ToList() after the Include. However, this may perform very poorly if you have a lot of tasks, since all your tasks are fetched from the database and only filtered afterwards.

You should try to use the approach shown by @FelipeOriani, since in that case only the relevant tasks are fetched from the database.

Upvotes: 2

Felipe Oriani
Felipe Oriani

Reputation: 38618

Try using the Any method where you can specify a column to compare, for sample:

var taskModels = db.Tasks
                   .Where(t => t.Managers.Any(m => m.Id == currentUser.Id))
                   .Include(t => t.Autor) 
                   .ToList();

Don't forget to call a method to execute this statement, such as ToList() or FirstOrDefault().

Upvotes: 3

Related Questions