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 Articles
- C# LINQ query creating inefficient SQL compared to original query
- C# Linq query help removing foreach loops creating cleaner code
- creating Linq to sqlite dbml from DbLinq source code
- Query generated by LINQ is terrible slow compared by creating your own SQL view
- LINQ query to perform a projection, skipping or wrapping exceptions where source throws on IEnumerable.GetNext()
- Error creating a Linq query
- Does this LINQ code perform multiple lookups on the original data?
- Is there any way to create a LINQ query as a variable without having the data source (yet)?
- Creating a new object in LINQ query
- LINQ Source Code Available
- Create Linq Expression for Sql Equivalent "column is null" in c# by creating linq query dynamically
- linq - how do you do a query for items in one query source that are not in another one?
- How can I write the following code more elegantly using LINQ query syntax?
- Why is LINQ Where search query faster on List<> compared to ConcurrentBag<>
- Creating multiple results from Linq query
- How to dynamic add filters to a LINQ query against an Odata Source in C#
- Use a linq query as microsoft local report Data Source (WinForms)
- Determine the source DataContext for a Linq to Sql query
- Linq is this string.Compare in the query inefficient and is there a better way?
- LINQ query returns old results when source list is re-initialized
- Are LINQ .OrderBy().ThenBy() Operators redundant when the original Query uses the ORDER BY clause?
- How to get SQL query into LINQ form in C# code
- How can I code a Linq query to do an upward Include?
- Creating dynamic Linq query based on property values
- Updating the original reference to an object that was returned from a linq query
- Linq Query - Creating a generic Subset
- Identify source of linq to sql query
- NHibernate LINQ query performance, which code fragment is better?
- Linq sub query when using a repository pattern with EF code first
- Linq making very inefficient Entity Framework query
- LINQ implement multiple select where condition
- Is possible to use generics in LINQ-to-SQL mapping?
- Autocomplete textbox freezes while executing query. Must be a better way!
- Linq query to select properties only of type?
- Linq or Lambda - Method to allow custom Orderby clause on joined table
- Linq2XML separate keys into List
- How can i achieve this in LINQ-Queries?
- LINQ - Cannot implicity convert type, An explicit conversion exsist
- Comparing times with LINQ to sql dynamically
- algorithm to consolidate overlapping range
- Entity & LINQ Method Chain Query
- How to build nested view model via LINQ query
- Linq Left Outer Join - DefaultIfEmpty Error
- Logic to decipher consecutive date ranges
- Temporary variable inside anonymous type
- Custom LINQ extension for make join
- LINQ: differences between single Where with multiple conditions and consecutive Wheres with single condition
- Custom method in IQueryable<T>.Select
- LINQ GroupBy and project to type with both a list of its keys and result of ToDictionary
- How to combine 2 tables using Entity Framework 6 and Linq in an MVC Project?