Reputation: 2009
I am getting the following error on the word "join" in the code below.
The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'.
var organisationQuery = ClientDBContext.Organisations.Where(x => true);
var orderGrouped = from order in ClientDBContext.Orders.Where(x => true)
group order by order.OrganisationId into grouping
select new { Id = grouping.Key.Value, OrderCount = grouping.Count() };
var orders = from og in orderGrouped
join org in organisationQuery on og.Id equals org.Id
select(x => new OrganisationOrdersReportPoco()
{
OrganisationNameThenCode = org.Name,
TotalOrders = og.OrderCount
});
I don't see a problem with the join clause? Can anyone please advise?
Edit: This is the piece of SQL I am attempting to write as LINQ.
SELECT grp.OrganisationId,
grp.OrderCount,
organisations.Name
FROM (select OrganisationId,
count(*) as OrderCount
from orders where 1 = 1 group by OrganisationId) grp
LEFT OUTER JOIN organisations on grp.OrganisationId = organisations.OrganisationId
WHERE 1 = 1
I have complicated where clauses on both orders and organisations... simplified for this example.
Upvotes: 0
Views: 1662
Reputation: 2009
(Credit to Giorgi Nakeuri)
I was confusing LAMBDA with LINQ expressions.
Replacing my select with this solved it.
select new OrganisationOrdersReportPoco()
{
OrganisationNameThenCode = org.Name,
TotalOrders = og.OrderCount
};
Upvotes: 0
Reputation: 273854
You are selecting into an anonymous type in the first query:
var orderGrouped = ..
select new { Id = grouping.Key.Value, OrderCount = grouping.Count() };
This 'breaks' the connection with order
.
The join
looks like it should work for Linq-to-Objects but it can't be converted into SQL.
You'll have to eliminate the anonymous type and somehow make a more direct connection.
I wonder why you don't simply go from Organisations? With a proper mapping using nav-properties it should look like:
from org in ClientDBContext.Organisations
select(x => new OrganisationOrdersReportPoco()
{
OrganisationNameThenCode = org.Name,
TotalOrders = org.Orders.Count
};
using the Id properties should be a little more involved but follow the same pattern.
Upvotes: 2