Talen Kylon Talen Kylon - 1 month ago 12
C# Question

Adding WHERE statements to a LINQ lambda expression

The two tables I'm working with here are built like this:

Users Table PurchaseLog Table

ID| LDAP| FNAME| LNAME| ID| UserID| PurchaseID | LOCATION |TIME


I'm trying to build a query that gets me back each user and the number of purchases they have. The model I'm trying to fill:

public class UserPurchaseCount{
public string LDAP {get; set;}
public int Count {get; set;}
}


This SQL query I wrote appears to be returning the correct results,

Select Users.LDAP, count(*) as Purchases
FROM Users
JOIN PurchaseLog ON PurchaseLog.UserId=Users.ID
WHERE Users.FName NOT LIKE '%name%'
AND PurchaseLog.CreatedDate <= 'some end date'
AND Purchase.CreatedDate >= 'some start date'
GROUP BY Users.LDAP


I suck at lambdas, but I like them and want to get a better understanding of them. Here's what I've got so far:

var items = db.PurchaseLogs
.Join(db.Users, usr => usr.UserId, x => x.ID, (usr, x) => new { usr, x })
.GroupBy(y => new { y.x.LDAP})
//.Where(i => i.CreatedDate >= startDate)
//.Where(i => i.CreatedDate <= endDate)
//.Where(i => i.FName.Contains("Guy1", StringComparison.CurrentCultureIgnoreCase) == false)
.Select(g => new
{
g.Key.LDAP,
Count = g.Count()
})

.ToList();


This lambda expression works. When I uncomment the WHERE clauses, the compiler throws up in my face.

Error 6 'System.Linq.IGrouping<AnonymousType#2,AnonymousType#3>' does not contain a definition for 'FName'...

Answer Source

Don't group before apply the conditions:

var items =db.PurchaseLogs
             .Join(db.Users, usr => usr.UserId, x => x.ID, (usr, x) => new { usr, x })
             .Where(i => i.user.CreatedDate >= startDate)
             .Where(i => i.user.CreatedDate <= endDate)
             .Where(i => !i.x.FName.Contains("Guy1"))
             .GroupBy(y => new { y.x.LDAP})
             .Select(g => new
                         {
                          g.Key.LDAP,
                           Count = g.Count()
                          })

             .ToList();