[EDITED: I left the original question below, with some more context and code to reproduce the problem. The short version below contains the essence of the question]
Short version: the query below throws a System.NotSupportedException: "Unable to cast the type 'System.Linq.IOrderedQueryable1' to type 'System.Linq.IQueryable1'. LINQ to Entities only supports casting Entity Data Model primitive types." The exception is only raised in the VB.Net version. When translated to C#, no exception is raised.
Dim doesThisCrash = From outerOrder In orders
Where outerOrder.ProductId =
(From p In products Join o In orders On p.Id Equals o.ProductId
Order By p.Id
Select p.Id).FirstOrDefault()
Select outerOrder
doesThisCrash.ToList()
So, to make it crash, it seems that we need a subquery where the original ObjectSet (orders) is joined with another ObjectSet (products), and ordered. When using just the orders or the products set, no crash occurs. When leaving out the Order By, also no crash.
I'm inclined to think this is a (VB.Net) compiler bug, unless there is something obvious that I'm overlooking here...
For now my question still stands:
- why does a seemingly exact same query work in C# but not in VB?
- can this query be made to work in VB.Net?
[/EDIT]
Optional, longer version (original question):
My domain looks very different, but I translated the problem to a simpler version, with the following entities (note: I actually defined these using the .edmx designer, so this is a simplified version):
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime DateCreated { get; set; }
}
public class Order
{
public int Id { get; set; }
public int CustomerId { get; set; }
public int ProductId { get; set; }
public DateTime OrderDate { get; set; }
}
public class Customer
{
public int Id { get; set; }
}
I'm trying to work out a linq-to-entities query that should strucurally look like this, in VB.Net:
Dim db = New SampleEntities()
Dim orders As IQueryable(Of Order) = db.Orders
Dim products As IQueryable(Of Product) = db.Products
Dim currentDate = DateTime.Now
Dim qLinq = From outerOrder In orders
Where outerOrder.OrderDate = currentDate AndAlso
outerOrder.ProductId =
(From p In products Join o In orders On p.Id Equals o.ProductId
Where o.OrderDate = outerOrder.OrderDate AndAlso
outerOrder.CustomerId = o.CustomerId
Order By p.DateCreated
Select p.Id).FirstOrDefault()
Select outerOrder
This raises a System.NotSupportedException:
"Unable to cast the type 'System.Linq.IOrderedQueryable1' to type 'System.Linq.IQueryable1'. LINQ to Entities only supports casting Entity Data Model primitive types."
When leaving out the 'Order By' part, no exception is raised.
I don't really see a reason why this query would not be supported... So I decided to try the same thing in C#:
var qLinq = from oOut in orders
where oOut.OrderDate == currentDate
&& oOut.ProductId ==
(from p in products join o in orders on p.Id equals o.ProductId
where oOut.OrderDate == o.OrderDate
&& oOut.CustomerId == o.CustomerId
orderby p.DateCreated
select p.Id).FirstOrDefault()
select oOut;
To my surprise, this works! Then I translated the C# query to extension method syntax, and then back to VB, but got the same results (the C# version works, the VB.Net version raises the same exception).
So I guess my question is twofold:
- why does a seemingly exact same query work in C# but not in VB?
- can this query be made to work in VB.Net?
For reference, here are the queries in extension method syntax:
C# version:
var q = orders.Where(outerOrder =>
outerOrder.OrderDate == currentDate &&
outerOrder.ProductId ==
(products
.Join(orders,
f => f.Id,
o => o.ProductId,
(f, o) => new { f, o })
.Where(t => t.o.OrderDate == outerOrder.OrderDate
&& outerOrder.CustomerId == t.o.CustomerId)
.OrderByDescending(t => t.f.DateCreated)
.Select(t => t.f.Id))
.FirstOrDefault());
VB.Net version:
Dim q = orders.Where(Function(outerOrder) outerOrder.OrderDate = currentDate AndAlso
outerOrder.ProductId = (products.Join(orders,
Function(p) p.Id,
Function(o) o.ProductId,
Function(p, o) New With {.p = p, .o = o}).
Where(Function(x) x.o.OrderDate = outerOrder.OrderDate AndAlso
outerOrder.CustomerId = x.o.CustomerId).
OrderByDescending(Function(x) x.p.DateCreated).
Select(Function(x) x.p.Id).
FirstOrDefault()))
Moving the Order By p.DateCreated line as per my edited answer allows the query to run without any exceptions. However, the emitted SQL is different so I don't think you are getting back the correct result.
Dim qLinq = From outerOrder In orders
Let id = (From p In products
Order By p.DateCreated
Join o In orders On p.Id Equals o.ProductId
Where o.OrderDate = outerOrder.OrderDate AndAlso
outerOrder.CustomerId = o.CustomerId
Select p.Id).FirstOrDefault()
Where outerOrder.OrderDate = currentDate AndAlso
outerOrder.ProductId = id
Select outerOrder