1

Environment: VB.NET, ASP.NET 4.5, Entity Framework 5.0 Database First

I am trying to use VB’s Aggregate to determine the maximum value of a property in an entity which happens to be of type Date. In reality, this field should never be null – so I first set “Allow Nulls” to false in SQL Server and then when I generated my EF model it came through to the property as Nullable = False as would be expected.

When I then try to query to retrieve the MAX and the WHERE clause causes the return of an empty set, I get a “The cast to value type 'DateTime' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.”

Fair enough, I said – since in an empty set there would be no MAX, I’ll just make the result variable Nullable(Of Date) as has been referenced in other threads such as:

The cast to value type 'DateTime' failed because the materialized value is null

Dim documentMostRecentUpdate As Nullable(Of Date) = Aggregate d In EFContext.Documents
Where d.Enabled = False
Into Max(d.LastUpdated)

Interestingly, I still get the same error. Hmm. So, on a lark I try setting the column in SQL Server to Allow Nulls, regenerate my EF model, and all works fine. So, as per:

Entity Framework calling MAX on null on Records

I’ve tried various syntax versions, trying to get the concept of nullability into MAX such as

(d.LastUpdated(Of Date?))

But it will not compile. Also tried various variations on:

Dim documentMostRecentUpdate2 As Nullable(Of Date) = 
    EFContext.Documents.Where(Function(d) d.Enabled = False).Max(Function(d) d.LastUpdated)

without finding the right syntax to allow me to make the column/property not nullable again.

So, my question is:

is it syntactically possible in VB to leave the data column/entity property as not nullable while still using MAX on an empty set without it throwing an exception?

UPDATE: After some more searching, I found a way to achieve the underlying goal by moving away from Aggregate and moving to using From with the DefaultIfEmpty method:

Dim documentMostRecentUpdate = 
(From d In EFContext.Documents
Where d.Enabled = True
Select d.LastUpdated).DefaultIfEmpty.Max

and then checking to see if documentMostRecentUpdate = Nothing

This may be less than optimal, since I also need to compute other aggregates on the same result set and now require multiple queries instead of one (although perhaps the generated SQL has separate queries as well). So, my question remains within the context of the Aggregate keyword in VB.

Community
  • 1
  • 1
JimMSDN
  • 484
  • 4
  • 16
  • 1
    Does `Max(Function(d) CType(d.LastUpdated, Date?))` work? In C# I would use `Max(d => (DateTime?)d.LastUpdated)` which solves the empty collection problem. – Slauma Feb 17 '14 at 18:12
  • @Slauma unfortunately your suggestion (and a couple of other variants on the theme) is not valid syntax. Thanks for the idea, though. – JimMSDN Feb 18 '14 at 18:58
  • @Slauma: the conversion to `Nullable` on the fly worked. Thanks for the solution. Add that as an answer. :) – Leniel Maccaferri Sep 18 '14 at 22:22

0 Answers0