Need help with an linq statement.

Question

Hello,

I need help with an linq statement.

Im not able to write such a linq statement and just gave up trying :).

In this example it should return row 1 and 3, because i need all rows of a single 'type' that have the highest SUM of 'priority' and if 'priority is equal, the highest SUM of 'datetime' and where 'flag' isn't 10.

Thanks for reading.

Answer 1

Hi thanei;

I may not be understanding your requierments correctly but would you not also expect rows 4 and 8? Because they are the highest in there group and flag isn't 10?

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 2

Hi thanei;

Try this code snippet to see if it gives you what you are looking for.

TestDBEntities db = new TestDBEntities();

var results = (from t in db.Table_1
               group t by t.type into typeGroup
               from tg in typeGroup
               let maxPriority = typeGroup.Where(f => f.flag < 10).Max( p => p.priority)
               where tg.priority == maxPriority &&  tg.flag < 10
               select tg).ToList();

// Print a headder
Console.WriteLine("ID\tType\tPriority\tDateTime\tFlag\tData");
foreach (var table1 in results)
{
    // Print data line
    Console.WriteLine("{0}\t{1}\t\t{2}\t{3}\t\t\t{4}\t\t{5}", 
        table1.id, table1.type, table1.priority, table1.datetime, table1.flag, table1.data);
}

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 3

Hello,

Thank You for Your answer. I have tried out Your statement but thats not what I'm looking for.

I have written an SQL statement equivalent of the linq statement that I need. I think this will be a better way to show what i need in linq.

SELECT Q.[id], Q.[type], Q.[priority], Q.[datetime], Q.[flag], Q.[data]
FROM dbo.table1 Q
	INNER JOIN (
		 SELECT TOP 1 G.[type] AS selType, SUM(G.[priority]) prio1, SUM(G.[datetime]) date1
		 FROM dbo.table1 G
		 WHERE G.[flag] != 10
		 GROUP BY G.[type]
		 ORDER BY prio1 desc, date1 desc
	)
	T ON Q.[type] = T.[selType]
WHERE Q.[flag] != 10

Thanks!

Answer 4

Hello,

OK I used the Linqer Tool to generate the linq from my sql statement. Afer some optimizations the following linq works as desired:

(from q in db.hlsysswsendqueue
join t in
    (
        ((from g in db.hlsysswsendqueue
        where g.processingcount < 10
        group g by new
        {
            g.entitytype
        } into g
        orderby g.Sum(p => p.processingpriority) descending, g.Sum(p => p.processingdatetime) descending
        select new
        {
            top1Type = g.Key.entitytype
        }
        ).Take(1))
    )
on new { entitytype = q.entitytype } equals new { entitytype = t.top1Type }
where q.processingcount < 10
select q).ToList();

This statement is for an table with other column names but here are the machings

id = requestid

type = entitytype

priority = processingpriority

datetime = processingdatetime

flag = processingcount

data = entitystream

Hope someone will find this usefull.