LINQ to SQL complex query problem

Question

Hi all,

I have 3 tables: Principal (Principal_ID, Scale), Frequency (Frequency_ID, Value) and Visit (Visit_ID, Principal_ID, Frequency_ID). I need a query which returns all principals (in the Principal table), and for each record, query the capacity required for that principal, calculated as below:

Capacity = (Principal.Scale == 0 ? 0 : (Frequency.Value == 1 ? 1 : Frequency.Value * 1.8) / Principal.Scale)

I'm using LINQ to SQL, so here is the query:

from Principal p in ShopManagerDataContext.Instance.Principals
     let cap =
     (
          from Visit v in p.Visits
          let fqv = v.Frequency.Value
         select (p.Scale != 0 ? ((fqv == 1.0f ? fqv : fqv * 1.8f) / p.Scale) : 0)
     ).Sum()
     select new
     {
          p,
          Capacity = cap
     };

The generated TSQL:

SELECT [t0].[Principal_ID], [t0].[Name], [t0].[Scale], (
    SELECT SUM(
        (CASE 
            WHEN [t0].[Scale] <> @p0 THEN (
                (CASE 
                    WHEN [t2].[Value] = @p1 THEN [t2].[Value]
                    ELSE [t2].[Value] * @p2
                 END)) / (CONVERT(Real,[t0].[Scale]))
            ELSE @p3
         END))
    FROM [Visit] AS [t1]
    INNER JOIN [Frequency] AS [t2] ON [t2].[Frequency_ID] = [t1].[Frequency_ID]
    WHERE [t1].[Principal_ID] = [t0].[Principal_ID]
    ) AS [Capacity]
FROM [Principal] AS [t0]

And the error I get:

SqlException: Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

And ideas how to solve this, if possible, in one query?

Thank you very much in advance!

Answer 1

Here are 2 ways to do this by changing up your approach:

1. Create a user defined aggregate function using the SQL CLR. This may not be the right solution for you, but it's a perfect fit for the problem as stated. For one thing, this would move all of the logic into the data layer so LINQ would be of limited value. With this approach you get effeciency, but there's a big impact on your architecture.
2. Load Visit and Fequency tables into a typed DataSet and use LINQ to datasets. This will probably work using your existing code, but I haven't tried it. With this approach your achitecture is more or less preserved, but you could have a big efficency hit if Visit and Frequency are large.

Answer 2

Based on the comment, I've an alternative suggestion. Since your error is coming from SQL, and you aren't using the new column as a filter, you can move your calculation to the client. For this to work, you'll need to pull all the relevant records (using DataLoadOptions.LoadWith<> on your context).

To further your desire for use with binding to a DataGrid, it'd probably be easiest to bury the complexity in a property of Principal.

partial class Principal
{
    public decimal Capacity
    {
     get
     {
      return this.Scale == 0 ? 0 : this.Visits.Select(v => 
       (v.Frequency.Value == 1 ? 1 : v.Frequency.Value * 1.8) / this.Scale).Sum();
     }
    } 
}

Then your retrieval gets really simple:

using (ShopManagerDataContext context = new ShopManagerDataContext())
{
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Principal>(p => p.Visits);
    options.LoadWith<Visit>(v => v.Frequency);
    context.LoadOptions = options;

    return (from p in context.Principals
      select p).ToList();
}