dont understand this exception

Question

im using linq to sql i have a Mission entity, which holds a collection of assignments. lets say i went to query the total duration of all the assignments per mission.

and i've written the following query:

return db.Missions.Select(m => new MissionNameDays()
    {
        Name = m.MissionName,
        Days = m.Assignments.Sum(a => a.Duration())
    });

duration is simply defined as (to keep it simple)

public partial class Assignment
{
    public int Duration()
    {
        return 1;
    }
}

running this code gives me the following exception:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

i've noticed that if i omit the Duration() from the query (aka

Days = m.Assignments.Sum(a => 1)

it works OK
any ideas about the cause of this?

Answer 1

The Duration property is not expressible in a SQL statement since it isn't a table property. Since it is a method on Assignments, the expression parser cannot correctly turn it into a valid SQL expression. Presumably there is some column in the Assignments table from which Duration will be calculated. Try using the same expression that you use to calculate Duration in your Select expression.

return db.Missions.Select(m => new MissionNameDays()
    {
        Name = m.MissionName,
        Days = m.Assignments.Sum(a => a.LengthInMinutes / 1440 )
    });

Alternatively, you can finalize the query then do the select using LINQToObjects which will work with non-columnar properties/methods. Since you're not filtering the Missions in this query, I think I'd go with this method.

return db.Missions.ToList().Select(m => new MissionNameDays()
    {
        Name = m.MissionName,
        Days = m.Assignments.Sum(a => a.Duration())
    });