score:0
Accepted answer
thanks to ivan in the comments i came up with this solution:
var fouryearsago = datetime.now.addyears(-4).year;
var datawithoutgrouping = from m in maindata
where m.cid == "334r" && m.stmtdt.value.year > fouryearsago
join a in adjustments
on new {m.stmtdt, m.stmtno} equals new {a.stmtdt, a.stmtno} into grp
from ja in grp.defaultifempty()
select new {
dt = m.stmtdt,
no = m.stmtno,
fee = m.payamount,
adjustment = ja.amount
};
var data = (from b in datawithoutgrouping
group b by new {b.dt, b.no }into grp
select new {
statmentfee = grp.sum(x => x.fee),
statementadjustments = grp.sum(x => x.adjustment),
statementdate = grp.key.dt,
statementno = grp.key.no
}).tolist();
i just needed to use grp.key
as the firstordefault()
was creating a select for each record.
also, here is the answer in vb(i translated my question into c# as vb isn't that popular here and i translated it back) if anyone needs it:
dim fouryearsago = datetime.now().addyears(-4).year
dim datawithoutgrouping = from m in dbcontext.maindatas
where m.cid = "334r" andalso m.stmtdt.value.year > fouryearsago
group join a in dbcontext.adjustments
on new with {m.stmtdt, m.stmtno} equals new with {a.stmtdt, a.stmtno} into group
from ja in group.defaultifempty()
select new with {
.dt = m.stmtdt,
.no = m.stmtno,
.fee = m.payamount,
.adjustment = ja.amount
}
dim data = (from b in datawithoutgrouping
group b by grpkeys = new with {b.dt, b.no} into group
select new with {
.statmentfee = group.sum(function(x) x.fee),
.statementadjustments = group.sum(function(x) x.adjustment),
.statementdate = grpkeys.dt,
.statementno = grpkeys.no
}).tolist()
it generates this sql:
declare @p0 varchar(1000) = '334r'
declare @p1 int = 2014
-- endregion
select sum([t2].[payamount]) as [statmentfee], sum([t2].[value]) as [statementadjustments], [t2].[stmtdt] as [statementdate], [t2].[stmtno] as [statementno]
from (
select [t0].[stmtdt], [t0].[stmtno], [t0].[payamount], [t1].[amount] as [value], [t0].[cid]
from [maindata] as [t0]
left outer join [adjustments] as [t1] on ([t0].[stmtdt] = [t1].[stmtdt]) and ([t0].[stmtno] = [t1].[stmtno])
) as [t2]
where ([t2].[cid] = @p0) and (datepart(year, [t2].[stmtdt]) > @p1)
group by [t2].[stmtdt], [t2].[stmtno]
score:0
this is the result i got:
if you have already your ef
for maindata and adjustment
i created a list:
list<maindata> mdata = new list<maindata>();
list<adjustments> adj = new list<adjustments>();
list<result> resfinal = new list<result>();
and the linq
var gety = datetime.now.addyears(-4);
var res = from h in mdata
join j in adj
on h.stmtno equals j.stmtno
select new result { stmtdate = h.stmtdate, stmtno = h.stmtno, fee = h.fee, adj = j.adj, total = (h.fee * j.adj) };
resfinal = res.cast<result>().where(x=>x.stmtdate > gety).tolist();
the final class holder as a result
public class result
{
public datetime stmtdate { get; set; }
public int stmtno { get; set; }
public double fee { get; set; }
public double adj { get; set; }
public double total { get; set; }
}
Source: stackoverflow.com
Related Query
- C# LINQ query creating inefficient SQL compared to original query
- Query generated by LINQ is terrible slow compared by creating your own SQL view
- Create Linq Expression for Sql Equivalent "column is null" in c# by creating linq query dynamically
- C# Linq query help removing foreach loops creating cleaner code
- Determine the source DataContext for a Linq to Sql query
- How to get SQL query into LINQ form in C# code
- creating Linq to sqlite dbml from DbLinq source code
- Identify source of linq to sql query
- convert linq to object query to sql query (no linq to sql code or datacontext)
- What SQL query or LINQ code would get the following data?
- Help creating a LINQ to SQL query
- Convert SQL update query to c# LINQ code
- EF Linq QUery cause lock compared to SQL
- Join query creating problems in Linq to SQL
- How are people unit testing code that uses Linq to SQL
- Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator
- LINQ to SQL query using "NOT IN"
- how to see generated sql from a linq query
- How to write linq query to match SQL like select top 100 * from tab?
- How do I most elegantly express left join with aggregate SQL as LINQ query
- LINQ To SQL exception: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains operator
- LINQ query to perform a projection, skipping or wrapping exceptions where source throws on IEnumerable.GetNext()
- Steps for a beginner to run very basic linq to sql query using Linqpad
- linq to sql recursive query
- Extract sql query from LINQ expressions
- Convert SQL to LINQ Query
- LINQ to SQL query not returning correct DateTime
- Calling a SQL User-defined function in a LINQ query
- How to force LINQ to SQL to evaluate the whole query in the database?
- Error creating a Linq query
More Query from same tag
- Batchify long Linq operations?
- How to mock an IEnumerable<interface> type and pass it to constructor
- Linq - Group by multiple tables
- monotouch UIPickerView not loading its rows
- How to convert NUMBER(9) to String with Linq to Entity?
- Move record from a Table to another using foreach in C#
- Get value from sublist using Linq
- Get X records from complex LINQ query
- Why is LINQ not letting me OrderBy one of my data points?
- Linq XML query with nested loop - 1
- Find Date Overlapping in DataTable Using Linq C#
- Selecting Parent and Child List into a view model using LINQ or Fluent
- Specification Pattern with Expressions causing error - variable 'x' of type '' referenced from scope '', but it is not defined
- Datatable C# - How to get detail record from one table and its associated sub record from another table based on ID
- Get unique element from second list
- LINQ and how to return a list of a specific type
- Pass a different list types to method and loop through it like array
- How to query database using LINQ to bring data from database based on array of months in ASP.Net MVC 5?
- how to set auto increment value=true in LINQ
- Is this LINQ based search safe against SQL injection / XSS attack?
- C#: Is there a LINQ way to create an array of objects given an array of constructor parameters?
- Entity Framework - Selective Condition on Included Navigation Property
- Select only if condition in LINQ
- Select entire duplicate row from datatable with linq c#
- How to write Linq Queries for CRUD operations using Entity Framework with Asp.Net?
- Proper LINQ syntax to order Ienumerable based on value in Icollection
- Where is the implicit cast from TDelegate to Expression<TDelegate> declared?
- Linq duplicate removal with a twist
- IEnumerable.Except() and a custom comparer
- LINQ: Get all members with LAST order failed