score:3
it was somewhat confusing to see that your query didn't seem to correspond to the data. so instead, this will be based on the data alone. the query should produce valid sql so you won't have to use linq to objects. you can adapt it to your tables with little modification.
var query = from pred in predictions
group pred.points by pred.weeknum into week
join pred in predictions
on new { weeknum = week.key, points = week.max() }
equals new { pred.weeknum, pred.points }
group 1 by pred.player into player
let wins = player.count()
orderby wins descending, player.key
select new
{
player = player.key,
wins,
};
score:0
this code seems to be what you need:
var result = this.datalist
.groupby(data => data.week)
.select(data=>
{
return data.groupby(item => item.name)
.select(item => new { name = item.key, sumpoints = item.sum(v => v.points) })
.orderby(item => item.sumpoints)
.firstordefault();
})
.groupby(_=>_.name)
.todictionary(_=>_.key, _=>_.count());
score:0
mine is quite long
var weeklytopscore = from eachmatch in footballpredictions
group eachmatch by eachmatch.week
into weekly
select new {week = weekly.key, topscore = weekly.max(match => match.points)};
var playerwins = from eachresult in weeklytopscore
join entry in footballpredictions
on eachresult.week equals entry.week
where eachresult.topscore == entry.points
group entry by entry.player
into winner
select new { player = winner.key, wins = winner.count() };
var result = from entry in playerwins
group entry by entry.wins
into summary
select new { player = summary.select(data => data.player).aggregate((cur, nex) => cur + ", " + nex), wins = summary.key};
score:0
just pass a datatable to following function (please note that the code is in c#) //dt should contain column points but not rank column
public static datatable getrankeddatatable(datatable dt)
{
var rankeddt = (from row in dt.asenumerable()
orderby row.field<string>("points")
select row).copytodatatable();
rankeddt.columns.add("rank");
int rank = 0;
for (int i = 0; i < rankeddt.rows.count - 1; i++)
{
rankeddt.rows[i]["rank"] = rank;
if (rankeddt.rows[i]["points"].tostring() != rankeddt.rows[i + 1]["points"].tostring())
{
rank++;
}
}
rankeddt.rows[rankeddt.rows.count - 1]["rank"] = rank;
return rankeddt;
}
score:2
try:
p.points.any()
instead of:
p.points != null
Source: stackoverflow.com
Related Query
- LINQ Query with grouping and ranking
- A LINQ query with grouping and filtering of special groups
- Grouping and Sum some field with Sub query in LINQ
- Multiple Grouping with IQueryable and returning a list of objects in C# linq query
- Linq query for a nested select statement with grouping and distinct
- SQL query with grouping and join into LINQ
- Linq query with grouping and Max date
- LINQ (to Entities) Query with Sorting and Grouping
- linq query with grouping and conditional result
- Transforming T-SQL Query into C# LINQ with joins on multiple conditions and also grouping on multiple conditions
- LINQ query with Distinct and Union
- Selecting multiple columns with linq query and lambda expression
- LINQ select query with Anonymous type and user Defined type
- Entity Framework - Linq query with order by and group by
- Linq Query with SUM and ORDER BY
- LINQ Query with GROUP and SUM
- LINQ - writing a query with distinct and orderby
- Simple sql to Linq query with group by and aggregate functions
- Translating query with GROUP BY and COUNT to Linq
- Get excel cell value with Row and Column Position through open xml sdk linq query
- LINQ query with GROUP BY and Count(*) into Anonymous Type
- LinQ query with multiple tables and extracting data
- LINQ grouping and aggregating data with conditions
- Using LINQ Dynamic Query Library with Dictionary<string, object> and .AsQueryable()
- LINQ query that combines grouping and sorting
- LINQ - 'Could not translate expression' with previously used and proven query condition
- Optimize/rewrite LINQ query with GROUP BY and COUNT
- Linq Query with Contains and Nullable Value
- LINQ Query with both CASE statement and SUM function
- Advanced LINQ Grouping and Projection Query for Hierarchical Data (EF 4.0 + LINQ + ASP.NET MVC + HighCharts)
More Query from same tag
- LINQ to SQL: intermittent AccessViolationException wrapped in TargetInvocationException
- Linq dynamic select column and table
- Comparing count of multiple lists of multiple types?
- LINQ find closest coordinates
- LINQ indexOf a particular entry
- Splitting a list of dates/weeks into a and b weeks with LINQ
- LINQ orderby on date field in descending order
- Using Linq to select distinct UserID and longer entry
- C# - dictionary - How to get max of specific class value in dictionary?
- Sequence contains no elements on http post
- Linq Select two colums of different types
- Optimize IQueryable query to let EF generate a single SQL query instead multiple. Child collection of an entity must contains a custom collection
- How to rewrite sql query to linq
- Splitting a large dataset into smaller groups
- C# Linq to XML check if element exists
- Issue on delete record from SQL Server database through C# program
- How to write nested query in Linq
- Sorting in Entity Framework for certain parameters
- Filter a list by using .StartsWith and a Regular Expression?
- Linq - Left outer join with dot notation
- Performing operations on a collection of string arrays
- Linq ToString() how do I convert?
- Get data in a specific date for a time range with a minute difference
- Transform a dictionary to another with a different key type
- C# List Lambda with index, object and DoStuff() / to array
- SQL into LinQ Stement involving SUM()
- LINQ and Array.Copy
- Union two models together causes "The specified LINQ expression contains references to queries that are associated with different contexts."
- ORA-00904 Linq generates error in SQL query for Oracle
- LINQ query to get all products with in Min & Max price range?