slightly complex many-to-many linq query got me stuck

So I'm on Linq-To-Entities with an asp.net mvc project.

I always get a little stumped with this sort of query.

My schema is:

ProductTag
+TagName
+<<ProductNames>>//Many-to-many relationship

ProductName
+FullName
+<<Tag>>//Many-to-many relationship

PurchaseRecord
+Amount
+<<ProductName>>//one productname can be linked to Many purchase records.

I need to get the sum of all purchases for a given tag.

This is what I've tried.

ProductTag thetag//could be some tag

decimal total = myentities.PurchaseRecords
                    .Where(x => thetag.ProductNames.Any
                         (a => a.FullName == x.ProductName.FullName))
                         .Sum(s => s.Amount);

I've tried changing a couple of things, tried using Contains, but I know I'm fundamentally wrong somewhere.

I keep getting :

Unable to create a constant value of type 'ProductName'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Update So with @Alexandre Brisebois's help below it worked simply like this:

var total= item.ProductNames.SelectMan开发者_运维技巧y(x => x.PurchaseRecords)
                                             .Sum(s => s.Amount);

When you get this sort of error, you need to do all evaluations outside of the linq query and pass the values in as variables.

the problem with your query is that thetag.ProductNames.Any() is out of context.

This evaluation is not converted to SQL since it is not a string/guid or int.

You will need to query for this object within your query and evaluate from this object.

I'm not sure if that was clear.

You would need to do something like

var query1 = (from x in tags where x.tagID = id select x.ProductNames)
                                                          .SelectMany(...)

The select many is because you are selecting a collection ProductNames and need to bring it back as a flat set/collection fo that you can do a .Any() on it in the next query.

Then use this and do an query1.Any(logic)

decimal total = myentities.PurchaseRecords.
                           Where(x => query1.Any
                                (a => a.FullName == x.ProductName.FullName))
                                .Sum(s => s.Amount); 

By doing this you will stay in linq to entity and not convert to linq to objects.

The ForEach is not an option since this will iterate over the collection.

you can use AsEnumerable method to perform certain portions of query in C# rather than on sql server. this is usually required when you have part of data in memory (Collection of objects) so using them in query is not easy. you have to perform part of query execution on .net side. for your problem plz try

decimal total = myentities.PurchaseRecords.AsEnumerable()
                    .Where(x => thetag.ProductNames.Any
                         (a => a.FullName == x.ProductName.FullName))
                         .Sum(s => s.Amount);

plz visit this link to find more about AsEnumerable