This question is locked. New answers and comments are not allowed.
I'm not sure if this is a "bug" or not, but I came across this issue. Before I go on, I must add that while I've been using LINQ for some time... I've just now "dove" into creating more complex queries. For assistance/debugging, I've been using LINQPad which has been incredibly helpful. Anyway, here we go:
Here was the query I built in LINQPad:
This ran perfectly fine and the generated SQL looked good. However, when I moved it into Visual Studio and ran the code there it threw this error: Failure: Joins can only involve base type or reference fields, move other conditions into the filter.
I'm not sure why the error was thrown.
However, modifying the code to the below ran fine:
Again, I don't know if this is a bug or by design. But just wanted to point it out...
- Andrew
Here was the query I built in LINQPad:
var qry = (from c
in
_ctx.Cases
join imp
in
_ctx.Implanteds on c.CaseID equals imp.CaseID
join prod
in
_ctx.Products on imp.ProductID equals prod.ProductID
join inv
in
_ctx.Invoices on c.InvoiceID equals inv.InvoiceID
join com
in
_ctx.Commissions on
new
{
LocID = inv.LocationID,
CatID = prod.CategoryID,
SurID = c.SurgeonID,
RepID = salesRepID
}
equals
new
{
LocID = com.LocationID,
CatID = com.CategoryID,
SurID = com.SurgeonID,
RepID = com.SalesRepID
}
where c.CaseID == caseID
select
new
SalesRepCommissionDue
{
LocationID = inv.LocationID,
CategoryID = prod.CategoryID,
SurgeonID = c.SurgeonID,
SellingPrice = imp.SellingPrice * imp.Quantity,
CommissionPct = com.CommissionPercent,
}
);
This ran perfectly fine and the generated SQL looked good. However, when I moved it into Visual Studio and ran the code there it threw this error: Failure: Joins can only involve base type or reference fields, move other conditions into the filter.
I'm not sure why the error was thrown.
However, modifying the code to the below ran fine:
var qry = (from c
in
_ctx.Cases
join imp
in
_ctx.Implanteds on c.CaseID equals imp.CaseID
join prod
in
_ctx.Products on imp.ProductID equals prod.ProductID
join inv
in
_ctx.Invoices on c.InvoiceID equals inv.InvoiceID
join com
in
_ctx.Commissions on
new
{
LocID = inv.LocationID,
CatID = prod.CategoryID,
SurID = c.SurgeonID
}
equals
new
{
LocID = com.LocationID,
CatID = com.CategoryID,
SurID = com.SurgeonID
}
where c.CaseID == caseID && com.SalesRepID==salesRepID
select
new
SalesRepCommissionDue
{
LocationID = inv.LocationID,
CategoryID = prod.CategoryID,
SurgeonID = c.SurgeonID,
SellingPrice = imp.SellingPrice * imp.Quantity,
CommissionPct = com.CommissionPercent,
}
);
Again, I don't know if this is a bug or by design. But just wanted to point it out...
- Andrew