LINQ to SQL - Group By/Where

I'm trying to implement a group messaging feature in ASP.NET MVC. I want to display a list of all threads for a specific ContactID, displaying the latest message in that thread (no matter who it's from). I've set up my table as below:

MessageID ThreadID MessageBody ContactID 10000004 300152,300160, msg1 300160 10000005 300152,300160, msg2 300160 10000008 300152,300160, msg3 300152

I was able to display the latest message grouped by ThreadID. Ex:

ThreadID Count LatestMessage 300152,300160, 3 10000008

However, if I add the Where clause before the group by (see below), it'll filter on ContactID first before doing the group by, and producing this result:

ThreadID Count LatestMessage 300152,300160, 2 10000005

Here's the code:

var result = from s in pdc.Messages where s.ContactID == contactID group new { s } by new { s.ThreadID } into d let maxMsgID = d.Max(x => x.s.MessageID) select new { ThreadID = d.Key.ThreadID, Count = d.Count(item => item.s.MessageType == GlobalConstants.MessageTypeText), LastMessage = d.Where(x => x.s.MessageID == maxMsgID) };

Is there a way to do the group by and then filter on ContactID?

-------------Problems Reply------------

var result = from s in pdc.Messages
where s.ContactID == contactID
group new { s } by new { s.ThreadID } into d
select new {
ThreadID = d.Key,
Count = d.Count(item => item.s.MessageType == GlobalConstants.MessageTypeText),
LastMessage = => x.s.MessageID).Max(),

Hope it can help you and have a nice day.

Category:linq to sql Views:0 Time:2010-11-15
Tags: linq to sql

Related post

  • LINQ to SQL - Grouping by hours 2009-02-22

    How can I group result of a LINQ to SQL query by hours considering that the column type is DateTime? --------------Solutions------------- Here's a solution for technical hours (context-less). var query = myDC.Orders .GroupBy(x => x.OrderDate.Hour)

  • LINQ To SQL Grouping 2009-04-27

    Can some kind soul please lend me the Linq To SQL query for the following T-SQL Query SELECT e.EmployeeName, MIN(a.SignInTime), MAX(a.SignOutTime) FROM Employee e LEFT OUTER JOIN Attendance a ON e.Id = a.EmployeeId AND CAST (CONVERT (varchar, a.SignI

  • LINQ To SQL "Group By" 2009-06-11

    I wonder if someone can help me. I want to replicate the following SQL query using LINQ in VB.Net.I'm a little unclear on how to do subqueries / aggregates. Thanks SELECT * FROM Server S INNER JOIN ServerHDD H ON S.Server_ID = H.Server_ID INNER JOIN

  • LINQ to SQL: Grouping and limiting a record set 2009-07-23

    I'm using LINQ to SQL and I have a stored procedure which brings back a result set that looks like so: Type Field1 Field2 5 1 1 6 2 0 21 0 0 I'm hoping to do a few things with this record set: 1) Have 3 groups of results, one that has values in both

  • How can I map my LINQ to SQL grouped result to this existing interface? 2010-01-05

    I have a LINQ to SQL query that returns a grouped collection of Sponsor objects like so: var result = ( from s in db.Sponsors join sl in sb.SponsorLevels on s.SponsorLevelId equals sl.SponsorLevelId select new Sponsor { Name = s.Name, Level = sl.Leve

  • How to use linq-to-sql group by to list duplicated records? 2010-08-25

    Suppose I have two tables: Category and Product. I want use linq-to-sql to select all categories (with products) that have duplicated products. My query goes like this: from p in db.Products group p by p.CategoryId into c select new { categoryId = c.

  • LINQ to SQL group by with take 2010-09-03

    I have a table that looks like this: Id GroupId Value and it has about 100 rows How can I return the top 10 rows for value but with no duplicating GroupId? --------------Solutions------------- This should do it: var results = table .GroupBy(x => x

  • LINQ to SQL Group by C# problem 2010-09-23

    I recently started to use LINQ to SQL and i have a minor complex query i need help with. I've got a table in my database called MovieComment, with the following columns: CommentID UserID MovieID Comment Timestamp So, what i wanna do is to group the c

  • Linq to SQL - grouping products by the total quantity ordered 2011-05-10

    I'm trying to get a list of 'popular products' on an ecommerce site using LINQ to SQL - products are ordered and become rows in a Redemption table, with a quantity column to indicate the quantity ordered. Here are the relevant bits of the tables: Pro

  • Enormous Sql Generated for Linq-to-Sql Grouping 2011-06-22

    i have one linq to sql query that simply joins three tables and then performs grouping. Here is the query (from s in mktActualSales join p in sysPeriods on s.PeriodID equals p.PeriodID join d in setupDesignations on s.PositionID equals d.DesignationI

  • LINQ-to-SQL - Group By Week in Date object 2011-10-19

    I am a writing a query in LINQ-to-SQL, and in this query is a Date column. I want to group the results by the 'Week'. How would I do this in LINQ-to-SQL? Thanks --------------Solutions------------- Assuming Gregorian calendar, "first day" week rule a

  • LINQ to SQL group people by age 2011-11-03

    Sample in C# and VB.NET are OK. I have a table "People" with the following columns: -FullName (nvarchar not null) -DOB (datetime null) I want to write a LINQ to SQL to group the people by age, like following result: Age 19: 4 ppl Age 20: 5 ppl Age 21

  • Linq to sql group by a number of records 2011-11-16

    Is there a way to create a linq to sql query to group by a Take parameter? For instance if I have a Table that has 20 records with one unique ID value from 1 to 20, i would like to get a group of records grouped by 5 records: Group 1: 1,2,3,4,5 Group

  • Linq-to-SQL - Grouping and joins 2011-11-19

    I have this schema: Products (**ProductId**, OwnerId, Name) Categories (**CategoryId**, Name) ProductsInCategories( **ProductId**, CategoryId ) When I wish to return the list of categories the Linq is simple enough: from c in db.Categories orderby c.

  • linq-to-sql grouping with timezone 2012-01-30

    I have a linq-to-sql query that groups results by DateTime, something like this: var TheQuery = from .... where .... group a by a.TheDateTime.Date in TheGroups The problem is that this groups by actual dates that are stored in UTC in the DB. Let's sa

  • Linq-to-SQL group by date-periods depending on a parameter (daily, Monthly-) 2012-02-20

    I'm trying to make a Linq to SQL query that returns Date grouping results. The challenge is about grouping daily/weekly/monthly/quarterly depends of a enumerable parameter (periodicity). Below, my current Linq to SQL query: var TotalGroupedInvoices =

  • Linq to SQL Grouping Child Relationships 2008-09-22

    I'm trying to run a LINQ to SQL query that returns a result in a grid view in a search engine style listing. In the simplified example below, is it possible to populate the collection with a comma-separated list of any children that the parent has (N

  • LINQ to SQL grouping and passing onto a view 2012-02-08

    I am new to Asp.Net, MVC3, Linq, and everything else related to it. I'm very used to PHP and ColdFusion so pardon my ignorance! Essentially what I am trying to do is re-create the situation in ColdFusion where I can perform the equivalent of a cfoutp

  • Linq-to-SQL Grouping not ordering correctly 2010-05-06

    Hi can someone help me convert this tsql statement into c# linq2sql? select [series] from table group by [series] order by max([date]) desc This is what i have so far - the list is not in correct order.. var x = from c in db.Table orderby c.Date desc

  • Linq to SQL: Group By and Sum() 2010-08-09

    I'm very new to linq so this should be pretty easy to answer, but I've had a hard time finding the answer. I have the following LINQ statement, which performs a simple linq query and assigns the resulting values labels on an web form: Dim db

Copyright (C), All Rights Reserved.

processed in 0.096 (s). 11 q(s)