Entity Framework Linq to Sql: select from 2 different subqueries based on boolean condition

Question

For example, a timesheet application: the model consists of a Project that can be configured to allow work against any Task, or only a specified subset of tasks.

When retrieving the data to log a timesheet for this Project, it needs to load the TaskOptions. Depending on the TasksMode of the Project, this could be all tasks in the database, or only the project's specified Tasks.

I have attempted the following EF linq query:

var availableTasks = dbContext.Projects
    .Where(p => p.ProjectId == projectId)
    .SelectMany(p => p.TasksMode == ProjectTasksMode.AllowAll
        ? dbContext.Tasks
            .Select(t => new TaskOption {TaskId = t.TaskId, Name = t.Name})
        : p.Tasks.Select(t => t.TaskId)
            .Join(dbContext.Tasks, id => id, t => t.TaskId,
                (tId, t) => new TaskOption {TaskId = tId, Name = t.Name}))
    .ToList();

However I get the following System.NotSupportedException:

Unable to cast the type 'System.Linq.IQueryable1[[MyApp.TaskOption, MyApp, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]' to type 'System.Collections.Generic.IEnumerable1[[MyApp.TaskOption, MyApp, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]'. LINQ to Entities only supports casting EDM primitive or enumeration types.

I'm not sure how I would write this in SQL either, so am struggling to work out how to debug it. I could just use 2 separate db queries, but I think this should be possible.

At the end of your join, try adding .AsEnumerable(). So, it'd be: (tId, t) => new TaskOption {TaskId = tId, Name = t.Name})).AsEnumerable() — Rob
– Rob ♦, Commented Oct 23, 2015 at 2:50
I don't have an answer to your question, but having a property ProjectTasksMode indicating that from a separate table "all data" vs "only where the FK of id matches" is something I'd try to resolve at the schema level. In other words, there's something smelly going on here indicating a totally different problem related to relational data. — Trevor Ash
– Trevor Ash, Commented Oct 23, 2015 at 3:46
@Rob I think that would turn it into 2 separate db queries. I can definitely do that, but trying to find a way to do it in 1 query. — Tyson
– Tyson, Commented Oct 23, 2015 at 8:09
@Atoms Can you be more specific with the schema issue? My Tasks table contains all possible tasks. The Project.Tasks navigation property points to an association table called ProjectTasks, that just links each project with a task, ie. a many - many relationship. That association table will only be populated when the project's TaskMode != AllowAll — Tyson
– Tyson, Commented Oct 23, 2015 at 8:12

will_w · Accepted Answer · 2015-10-23 03:34:27Z

1

Hmmm, if you have a ProjectId property as part of the Task object, I'd think you could try something like:

var availableTasks = dbContext.Projects
    .Where(p => p.ProjectId == projectId)
    .SelectMany(p => dbContext.Tasks
        .Where( t => p.TasksMode == ProjectTasksMode.AllowAll || (t.ProjectId == projectId))
        .Select( t => new TaskOption { TaskId = tId, Name = t.Name}))
    .ToList();

Although that where condition gets evaluated for every Task in the DB unless the SQL server is doing some magic optimization.

answered Oct 23, 2015 at 3:34

will_w

736 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyson Over a year ago

Interesting! But my Task table doesn't link to the Project table directly, rather there is a M:M association table ProjectTasks with FKs to both. So there is no FK on Tasks at all.

Hassan El-Zarkawy · Accepted Answer · 2015-10-23 02:45:37Z

0

I'm not 100% sure about this syntax, but the compiler is complaining from different thing!! It can't cast object of type IQueryable To type IEnumerable, so be sure to end the both queries with .ToList();

Again, i'm not sure about this syntax beside you have many typos in this example!

answered Oct 23, 2015 at 2:45

Hassan El-Zarkawy

1014 bronze badges

1 Comment

Tyson Over a year ago

Compiles fine for me. Calling ToList() on each subquery will just turn it into multiple db queries, which is what I'm trying to avoid.

Collectives™ on Stack Overflow

Entity Framework Linq to Sql: select from 2 different subqueries based on boolean condition

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related