Why a simple linq command is not executed at the second case?

Question

I have the following line at my code.

var dates = query.Select(x => EntityFunctions.DiffDays(query.Min(y => y.Date), x.Date));

However I think that the command query.Min(y => y.Date) is executed for every x.

So I want to do the following

    System.DateTime varMinDate = query.Min(y => y.Date);
    var dates = query.Select(x => EntityFunctions.DiffDays(varMinDate, x.Date))

The type of the field Date at my model is System.DateTime.

However, If I change it at the second way, It I get an exception

An exception of type 'System.NotSupportedException' occurred in System.Data.Entity.dll but was not handled in user code

Edit

The query mentioned above is the following

var query = (from b in db.StudentProgressPerDay
                         where b.Student.Equals(InputStudent)
                         orderby b.Date
                         select b);

Why is that? And how I can fix it?

Answer 1

No it only does it once, by doing query.ToString() we can see the SQL Entity Framework is going to be generating. Your query generates the following SQL

SELECT 
    DATEDIFF (day, [GroupBy1].[A1], [Extent1].[Date]) AS [C1]
    FROM  [dbo].[Foos] AS [Extent1]
    CROSS JOIN  (SELECT 
        MIN([Extent2].[Date]) AS [A1]
        FROM [dbo].[Foos] AS [Extent2] ) AS [GroupBy1]

You can see it does a single query to get the minimum value then it joins the result of that query to your DATEDIFF where it will reuse that single result over and over.

---

The test program

class Context : DbContext
{
    public DbSet<Foo> Foo { get; set; }
}

public class Foo
{
    public int FooId { get; set; }
    public DateTime Date { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        using (var context = new Context())
        {
            var query = context.Foo;
            var dates = query.Select(x => EntityFunctions.DiffDays(query.Min(y => y.Date), x.Date));

            var result = dates.ToString();

            Debugger.Break();
        }
   } 
}