CODEWITHSUNDEEP
c#.netlinqentity-frameworkentity-framework-4
Mike Christensen
Mike Christensen

Reputation: 91666

Need EF/LINQ query to get all tasks assigned to a certain user

I have a table called TPM_TASKS which contain all tasks, as well as a table called TPM_USER which contains all users. I then have a table called TPM_USERTASKS which contains a UserId and TaskId which stores the assignment of one task to one user.

I'm attempting to select all tasks that are assigned to a given TPM_USER entity. So far, I've tried:

TPM_USER user = UserManager.GetUser(context, UserId); //Lookup the user in the DB
var tasks = (from t in context.TPM_TASK.Include("TPM_USER")
             where t.TPM_USER.Contains<TPM_USER>(user)
             select t);

However, when I try to iterate into this I get the runtime exception:

Unable to create a constant value of type 'Entity.TPM_USER'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

I believe it doesn't like me passing in a TPM_USER object into Contains(). This probably makes sense, as it would have to generate a nested SELECT statement rather than an IN clause, which perhaps EF isn't capable of doing. However, surely there's a way to do a query like this, right?

NOTE: If you're clever, you're probably about to ask me why I don't just query TPM_USERTASKS and then join in the matching TPM_TASK instead. Well, I'd love to do this. However, I've been trying all morning to get EF to generate a TPM_USERTASK model and it simply won't. The table shows up in the model store, and I can define relationships against it. It doesn't show up in the list of entities I can add or refresh from the database, and I've even tried deleting the entire .EDMX file and creating it again. I'm guessing something that gets used in a many-to-many relationship can't also be a stand-alone entity?

Upvotes: 0

Views: 290

Answers (2)

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

Well, you've got to use Navigation properties.

In a many-to-many relation, this means that you must have a

public virtual ICollection<TPM_USER> TpmUsers {get;set;}

in your TPM_TASK class

and /or a

public virtual ICollection<TPM_TASK> TpmTasks{get;set;}

in you TMP_USER class.

If you don't, you won't be able to write a query joining the two entities.

With that, you can write your query as

var userTasks = context.TPM_TASK
                   .Where(task => task.TpmUsers
                         .Any(user => user.Id == UserId));

Upvotes: 2

mfussenegger
mfussenegger

Reputation: 3971

Your query would look something like this:

var tasks = (from t in context.TPM_TASK.Include("TPM_USER")
            where t.TPM_USER.Any(x => x.Id == user.Id)
            select t);

But that's somehow a little bit off anyway. The TPM_USER object should already have a reference to TPM_TASK. So user.TPM_TASKS should give you all the tasks of the given user, otherwise your model is somehow wrong.

TASKS many-to-many USERS means: One Task, many users. One user, many Tasks.

Or am I wrong?

Update:

If you really want to get the TPM_USERTASKS class in your model you'd have to add a separate Key/Id to it.

create table TPM_USERTASKS (
    ID      int identity(1,1)   not null
    UserId  int                 not null,
    TaskId  int                 not null,
    constraint [PK_TPM_USERTAKS] primary key (ID)
)

Upvotes: 2

Related Questions