score:1
Accepted answer
public class exampleclass
{
public int id1 { get; set; }
public int id2 { get; set; }
public int usage { get; set; }
public int usagethis { get; set; }
public int usagelast { get; set; }
}
list<exampleclass> listthismonth = new list<exampleclass>
{
new exampleclass{id1=1, id2=1,usage=7, usagethis=1, usagelast=0},
new exampleclass{id1=2, id2=2,usage=4, usagethis=2, usagelast=0},
new exampleclass{id1=3, id2=3,usage=1, usagethis=3, usagelast=0},
};
list<exampleclass> listlastmonth = new list<exampleclass>
{
new exampleclass{id1=1, id2=1,usage=3, usagethis=1, usagelast=1},
new exampleclass{id1=4, id2=4,usage=3, usagethis=4, usagelast=3},
new exampleclass{id1=2, id2=2,usage=1, usagethis=8, usagelast=6},
};
var result = listthismonth.select(a=>new {value=a, list=1})
.union(listlastmonth.select(a => new { value = a, list = 2 }))
.groupby(a => new { id1 = a.value.id1, id2 = a.value.id2 })
.select(x => new exampleclass
{
id1 = x.key.id1,
id2 = x.key.id2,
usagethis = x.any(o => o.list == 1) ? x.first(o => o.list == 1).value.usage : 0,
usagelast = x.any(o => o.list == 2) ? x.first(o => o.list == 2).value.usage : 0,
usage = x.sum(o=>o.value.usage)
}).tolist();
//id1 id2 current last sum
//1 1 7 3 10
//2 2 4 1 5
//3 3 1 0 1
//4 4 0 3 3
score:0
// left join the current month with the last month
var currentmonth =
from current in resourcesthismonth
join last in resourceslastmonth on new { current.subscriptionid, current.itemid } equals new { last.subscriptionid, last.itemid } into outer
from o in outer.defaultifempty()
select new resource
{
subscriptionid = current.subscriptionid,
itemid = current.itemid,
unitsthismonth = current.units,
unitslastmonth = o?.units ?? 0, // replace null with 0
effectiverate = current.effectiverate,
resourcename = current.resourcename,
unitofmeasure = current.unitofmeasure
};
// reverse of the first join. last month left join current month
var lastmonth =
from last in resourceslastmonth
join current in resourcesthismonth on new { last.subscriptionid, last.itemid } equals new { current.subscriptionid, current.itemid } into outer
from o in outer.defaultifempty()
select new resource
{
subscriptionid = last.subscriptionid,
itemid = last.itemid,
unitsthismonth = o?.units ?? 0, // replace null with 0
unitslastmonth = last.units,
effectiverate = o?.effectiverate ?? last.effectiverate,
resourcename = o?.resourcename ?? last.resourcename,
unitofmeasure = o?.unitofmeasure ?? last.unitofmeasure
};
// union them together to get a full join
var resources = currentmonth.union(lastmonth);
Source: stackoverflow.com
Related Query
- LINQ Join usage data from two data sets into one
- Linq join - show all data in one table and supplement with data from two others
- Left join on two Lists and maintain one property from the right with Linq
- linq query to join two tables and get the count from one table values from the other
- Linq & C# - Inserting distinct data from one class into another
- C# LINQ join with conditional where clause on two different data sets
- Using LINQ to XML, how can I join two sets of data based on ordinal position?
- Join using LINQ between two tables, return records from only one of them
- C# - Linq to combine (or) join two datatables into one
- Add data from two databases into one gridview
- MVC3 Linq updating two tables with data from one model
- LINQ - Join Two Entities into One IQueryable Complex Type - EF4 and ASP.Net MVC
- Linq join condition with multipe data from two table
- C# Linq join transforming the two entities into DTO but provide a count of one of those entities
- Merging data from two tables into one view
- LINQ Lambda Join Error - cannot be inferred from the usage
- Merge two List<object> into one List in Linq
- Linq to select data from one table not in other table
- LINQ EF Join query from 2 different data context
- How can I combine this code into one or two LINQ queries?
- Use C# Linq Lambda to combine fields from two objects into one, preferably without anonymous objects
- Combine two LINQ queries from different sources into single object
- Linq join() - Join two entities and select one
- LINQ to Entities, join two tables, then group and take sums of columns from both tables
- How can I compare two sets of data in linq and return the common data?
- VB.Net LINQ - left outer join between two datatables - limit to one row
- How to use LINQ to combine two or more collections into one collection
- Linq intersect or join to return items from one collection that have matching properties to another?
- C# Combine two lists with nested lists into one using LINQ
- How can I combine .Select from two LINQ expressions into one?
More Query from same tag
- How to get value from nested KeyValuePair from IMongoCollection
- How to achieve this using Lambda
- Get the last folder's ID from given file path string to act as the parent ID of a new folder being created
- How to ask Automapper to grab related record by inner join on Id field which is not a foreign key?
- Linq logic in the select statement
- Update gridview inline with LINQ
- Linq and IEnumerable lazy query. Efficiency lost?
- How to Convert SQL query to LINQ (to get last record each bookingStatus)
- How to write an "OR" within a Linq to Sql .Where()
- indices of elements which satisfy a given condition
- How to dynamically add or remove where clause in LINQ
- XDocument.Descendants("somethinghere").Count() method does not exist
- group by base on 2 element
- "unrecognized expression node array index linq" exception in LINQ to SQL
- Object Oriented Model on top of LINQ to SQL
- Can I generate a linq expression dynamically in C#?
- Observable LINQ inconsistent exceptions thrown
- Create GroupBy Statements Dynamically
- stating which conditions have been matched in linq where
- How to know whether there is any overlap in a collection of periods using LINQ
- Print group by key in Linq
- LinqToHql extension to NHibernate is not registering properly, getting System.NotSupportedException
- Proper Join/GroupJoin implementation
- LINQ to Entities does not recognize the method 'Int32 ToInt32(System.Object)' method, and this method cannot be translated into a store expression
- Query entity select, inner join and count
- Default groups for GroupBy linq method
- LinqToSql DataContext cache and memory usage
- Binding query to datagrid with WPF and Entity Framework
- Linq distinct equivalent
- C# - Concatenate an in memory IList and IQueryable?