score:0
Accepted answer
methinks you not quite understood what i mean in comment. so try add more explained sample
first variant group new {d, s}
var sales= from d in cxt.sopdespatchreceiptlines
join l in cxt.soporderreturnlines on d.soporderreturnlineid equals l.soporderreturnlineid
join o in cxt.soporderreturns on l.soporderreturnid equals o.soporderreturnid
join s in cxt.stockitems on l.itemcode equals s.code
join p in cxt.productgroups on s.productgroupid equals p.productgroupid
join c in cxt.slcustomeraccounts on o.customerid equals c.slcustomeraccountid
where d.despatchreceiptdate.year ==2014
orderby d.soporderreturnline.itemcode
//group d by d.soporderreturnline.itemcode into g
group new {d,s} by s.itemid into g
select new
{
itemid = g.key,
total = g.sum(d=>d.d.despatchreceiptquantity),
average = (g.sum(d=>d.d.despatchreceiptquantity))/3,
jan = g.sum(d => d.d.despatchreceiptdate.month==01 ? d.d.despatchreceiptquantity : 0),
feb= g.sum(d => d.d.despatchreceiptdate.month==02 ? d.d.despatchreceiptquantity : 0),
mar= g.sum(d => d.d.despatchreceiptdate.month==03 ? d.d.despatchreceiptquantity : 0),
stock = g.first(s=>s.s)
};
return sales;
second way by new {s.itemid, s.freestockquantity}
var sales= from d in cxt.sopdespatchreceiptlines
join l in cxt.soporderreturnlines on d.soporderreturnlineid equals l.soporderreturnlineid
join o in cxt.soporderreturns on l.soporderreturnid equals o.soporderreturnid
join s in cxt.stockitems on l.itemcode equals s.code
join p in cxt.productgroups on s.productgroupid equals p.productgroupid
join c in cxt.slcustomeraccounts on o.customerid equals c.slcustomeraccountid
where d.despatchreceiptdate.year ==2014
orderby d.soporderreturnline.itemcode
//group d by d.soporderreturnline.itemcode into g
group d by new {s.itemid, s.freestockquantity} into g
select new
{
itemid = g.key.itemid,
total = g.sum(d=>d.d.despatchreceiptquantity),
average = (g.sum(d=>d.d.despatchreceiptquantity))/3,
jan = g.sum(d => d.d.despatchreceiptdate.month==01 ? d.d.despatchreceiptquantity : 0),
feb= g.sum(d => d.d.despatchreceiptdate.month==02 ? d.d.despatchreceiptquantity : 0),
mar= g.sum(d => d.d.despatchreceiptdate.month==03 ? d.d.despatchreceiptquantity : 0),
stock = k.key.freestockquantity
};
return sales;
Source: stackoverflow.com
Related Query
- LINQ - database query using "into", how do I reference data prior to "into" in the select clause?
- how to fetch data from database using linq query for relationship 1:N and N:N (between 3 entity) in asp.net mvc EF code first?
- How can I check the number of calls to the database in LINQ query when using .NET Core and Code First?
- How to get multiple column value from database into a list using LINQ query
- How to query database using LINQ to bring data from database based on array of months in ASP.Net MVC 5?
- LINQ Query - How to map a resultset into another object using Select
- LINQ Query to insert data into the database
- How can I write the following code more elegantly using LINQ query syntax?
- How to get SQL query into LINQ form in C# code
- how to convert Database Hierarchical Data to XML using ASP.net 3.5 and LINQ
- Using LINQ query result for data source for GridControl c#
- How to flatten a multi level XML into a single level XML using c# code LINQ
- Can't add a new record with an integer value into database by using linq from code C#
- How can I code numerous MIN functions into one LINQ to DataSet query
- How to dynamically query the database using LINQ and a variable?
- How to retrieve data using Linq Query
- How to check linq query result against Sql Server Database for data already exist or not?
- Linq query to get data from Database using group by clause
- Insert large amount of data into database using LINQ
- How to show 2nd row on Data in Next Button Click in C# and XML database using LINQ
- How to get data of atleast one id match using linq query in c#
- How to update the data in database using linq
- How to get a strongly typed linq query of a data table and database
- How to Get data in repeater from multiple table using like linq query
- How to reinsert data from one table onto itself using LINQ in code migration?
- how to insert data in 1 table and update another table in a sql database using linq
- Insert data into database using LINQ
- How to get data from linq query using select
- How to successfully query a database for 0 applicants using LINQ
- Get data from database using linq query and display it in dynamically generated labels in repeater
More Query from same tag
- How to group by in LINQ?
- How to create a tree list from a list
- To write a linq query to get selected name count
- Combine two different lists C#
- using Navigation property to get Data
- Exclude collection for removal, Linq
- Translate sql with COUNT into LINQ
- output the corresponding unique element
- Is it possible to turn an IEnumerable into an IOrderedEnumerable without using OrderBy?
- Multiple "where" conditions in Entity Framework 4 statements - Possible?
- Add child to root XElement in XDocument
- Slow speed when using nested loops when comparing large data sets
- problem with conversion of output from linq query to list<> in asp.net
- How to selelect Element names with XDocument
- Checking if a table contains values in a list
- Linq.Dynamic Select custom column with operation
- LINQ Custom OrderLine Fields in uCommerce
- Why doesn't LINQ's First method simply return null if no item is found?
- Join multiple lists of objects in c#
- Using LINQ to SQL to query large table (10M+ rows) results in timeout
- Get uniques elements
- VB.net LINQ filter on multiple columns
- Translating query from SQL to EF Lambda expressions
- Linq dynamic where count?
- How to fix System.Xml.Linq.XContainer.Element(...) returned null
- Replace value in DataColumn
- Return partial linq query from method - how to declare return type
- If string in list occurs in string, then add to list
- Convert regex matches to the list of strings
- Flatten list of dynamic objects in c#