score:2
Accepted answer
first, don't use methods like firstordefault()
on groupby
result - they are not translatable. use only key(s) and aggregate functions (because that's what sql group by
operator supports).
second, use temporary projection (select
) for groupby
result containing the key/aggregates needed, then join it to another entities (tables) to get the additional info needed for the final projection.
e.g.
from v in journalvoucherlines
group v by v.accountid into vg
select new // <-- temporary projection with group by fields needed
{
accountid = vg.key,
credit = vg.sum(v => v.credit),
debit = vg.sum(v => v.debit),
} into vg
join bp in parties on vg.accountid equals bp.accountid // <-- additional join(s)
select new
{
name = bp.fullname,
key = vg.accountid,
creditor = vg.credit,
deptor = vg.debit,
remainamount = vg.credit - vg.debit
};
which translates successfully to
select [p].[fullname] as [name], [t].[accountid] as [key], [t].[c] as [creditor], [t].[c0] as [deptor], [t].[c] - [t].[c0] as [remainamount]
from (
select [j].[accountid], sum([j].[credit]) as [c], sum([j].[debit]) as [c0]
from [journalvoucherlines] as [j]
group by [j].[accountid]
) as [t]
inner join [parties] as [p] on [t].[accountid] = [p].[accountid]
update: the same linq query with method syntax is even straight forward:
var query = journalvoucherlines
.groupby(v => v.accountid)
.select(vg => new
{
accountid = vg.key,
credit = vg.sum(v => v.credit),
debit = vg.sum(v => v.debit),
})
.join(parties, vg => vg.accountid, bp => bp.accountid, (vg, bp) => new
{
name = bp.fullname,
key = vg.accountid,
creditor = vg.credit,
deptor = vg.debit,
remainamount = vg.credit - vg.debit
});
but if you need more joins, query syntax is preferable.
Source: stackoverflow.com
Related Query
- How can I use Linq expression for Join with GroupBy in EF Core 3.1
- How can i use Linq expression for Join on multiple table in EF Core 3.1
- How can I use this Linq Query using it with a multiple parameter by Join tables
- How does LINQ expression syntax work with Include() for eager loading
- How can I use Linq with a MySql database on Mono?
- LINQ How to define a default type for use with ElementAtOrDefault Operator
- How to use a Func in an expression with Linq to Entity Framework?
- How can you update a Linq Expression with additional parameters?
- How can I use Left join in linq that we use in sql?
- How to reuse a linq expression for 'Where' when using multiple source tables
- How can I use linq to return integers in one array that do not match up with an integer property of another array?
- Can i use join with let in linq - c#
- How can I switch that code to use LINQ
- How can I implement a LEFT OUTER JOIN in LINQ using lambda syntax on Entity Framework Core 2.0?
- How can i use linq for parsing string datetime to real datetime?
- How can I code an outer join using LINQ and EF6?
- How to use Expression for Linq union and intersect?
- How to use an expression with a generic func lambda in a linq where clause?
- How make LINQ query with GroupBy and LEFT JOIN
- How can I use LINQ with a class that has parallel arrays?
- How can I use Linq to join between objects and entities?
- How to use Linq to join entity tables with a cross-reference table
- How can you use LINQ to find Azure AD users with specific licenses using the Azure AD Graph API Client Library 2.0
- How can I select using LINQ for an entry that contains a LIST with more than one row?
- How can i use linq to make a console application in C# for counting vowel in a sentence
- LINQ extension method for multiple join with multiple GroupBy requirements
- How can I refactor this code for LINQ filtering?
- How can I check the number of calls to the database in LINQ query when using .NET Core and Code First?
- How can you use Linq result with RDLC report?
- How can I use external expressions in Linq with EF4 (and LINQKit)?
More Query from same tag
- Linq query to filter out list of list without using Remove
- LINQ/C# - Making a DTO from a collection?
- Find list value against certain key in Dictionary
- Filter tree list retaining parent using LINQ
- How to write a lambda to get one property based on another property in an object
- Read many text files into SQL Server Express
- Cannot convert from 'System.Linq.Expressions.LambdaExpression' to 'System.Linq.Expressions.Expression
- DbSet not seeing LINQ definitions
- Using DateDiff with Linq.Dynamic library for fetching today records
- Converting to decimal and then to varchar in linq to sql
- How to Implement GetEnumerator method for class that implements IEnumerable<IEnumerable<T>>
- LINQ to Entities - How best to obtain the IDENTITY value after calling SaveChanges()
- Entity Filter child without include
- Query dynamic data with LINQ
- Select first word of list of strings and concat last char of the rest of items on list in linq
- How can I get all the children records in a single query if I need to lookup the parent ID?
- Concatenate All Properties to a String with LINQ
- "Selecting" or "Wrapping" an IQueryable so that it is still queryable
- Comapring two List<String> to extract the common items and put it in List
- Linq to order by number of matches
- Is there a better way to write a LINQ function?
- c# OrderBy string hierachy xx.xx.xx
- The cast to value type 'Int32' failed
- Enum.ToString() not working in tooltip
- Why am I able to edit a LINQ list while iterating over it?
- object' does not contain a definition for MainTypeCode for dynamic data
- How to merge object properties of the same class together via LINQ
- How do I check composite ID exists?
- How can I group by Linq statement
- Is it possible to determine if a LinqToSql context was disposed?