LINQ Help

[cotton31]

To dive right in, here's a simplified version of my database:

Category
- ID
- CategoryName

SubCategory
- ID
- SubCategoryName
- ParentCategoryID

Item
- ID
- ItemName

ItemCategory
- ID
- ItemID
- SubCategoryID

In my UI, I want to display each Category along with the number of items linked to it.

The user can then click on the category and see that total broken down by each Sub Category.

My question is, how do I get this data using LINQ?

In plain SQL, I'd do something like:

SELECT CategoryName
, SubCategoryName
, COUNT(ItemID)

FROM Table

GROUP BY CategoryName
, SubCategoryName

How would I do something similar in LINQ? I'm assuming that the advantage of LINQ would be that it's object based, so I could have a count of an ItemCategory object, and then link back through each Category in my UI?

This is in C#.Net by the way (MVC5)

Sorry if I've explained this really badly!

 

[cotton31]

My advice would be to make your SQL in to a stored procedure and call that. LINQ is pretty slow at this stuff. I'd expect an SP to be an order of magnitude faster.

Even when using MVC5? I'm pretty new to MVC but it all seems to revolve around LINQ?

 

[cotton31]

I'm a bit confused by LINQ to be honest, it doesn't seem to be as useful as it should be...

For example, I don't seem to be able to traverse through all the joins in my tables - so from a list of items (to take my example above) I'd expect to be able to get to it's SubCategory and then it's related Category?

It may be how I have my database set up (it was a Database First config)

 

[cotton31]

Haircut - sorry it's LINQ to EF, using EF6.

 

[cotton31]

xirokx - I've just watched that and I already understood what it was telling me. However it did seem to do (in the example shown) what I expected LINQ to be able to do which is traverse across different linked objects.

In my application, I have a Projects table, a Person table and a Categories table.

A Project can have many Persons or Categories assigned, just as Persons and Categories can have many Projects.

So I have a ProjectCategories table, and a ProjectPeople table to link them.

In LINQ I expected to be able to access the Person objects that are linked to the Project I have (from a List or wherever).

However, if I do something like:

List<Person> = new List<Person>(db.Projects.Select(p => p.ProjectPeople.[b]***here I'd expect to see Person object properties***[/b]

Is that not correct?

Thanks for your comments!

 

[cotton31]

Haircut does seem to understand my issue though - I'm unable to access foreign keys of the ProjectCategories object, so I'm unable to do Project.ProjectCategories.Category.Name for example.

What I actually need, is a list of all Categories with the number of Projects assigned to them, even if a Category has zero Projects, it still needs to be in the list.

Let me know if I'm going about tackling this the wrong way!

 

[cotton31]

Haircut - as far as I'm aware, I have the correct FK's in the database, and this has created navigational properties in each class, but something has gone wrong somewhere!

Philldutton - the code is at work but I'll try and post tomorrow. I'm using Database First. From what I understand it should be straight forward e.g.(db.Projects.ProjectCategories.Category)?

 

[cotton31]

davetherave2 - you have absolutely NOT got the wrong end of the stick! Stupid mistake on my part as it's very obvious now you've said it, however I actually have two levels of Category.

Category & SubCategory (1 Category can have many SubCategories)
ProjectCategories links Projects and SubCategories (not Category's)

So, your code works perfectly for SubCategories, as they're the PK and therefore it returns a list of SubCategories along with a Total.

However I also need the same for Categories. I expected to be able to amend your code to the following:

db.Categories.Select(category => new
                {
                    CategoryName = category.Description,
                    NumberOfProjects = ([b]category.SubCategories.ProjectCategories[/b].Any() ? category.ProjectCategories.Count() : 0)
                }
            ).ToList();

The bit in bold doesn't work though - I'm not able to traverse through those navigational properties (is that correct?)

If I do this:

var subs = db.SubCategories.Select( subcategory=> new    
                { 
                    CategoryName = subcategory.Category.Description, 
                    NumberOfProjects = (subcategory.ProjectCategories.Any() ? subcategory.ProjectCategories.Count() : 0)
                }
            ).ToList();

I get the following returned:

Cat 1 - 5
Cat 1 - 6
Cat 1 - 1
Cat 2 - 4
Cat 2 - 9
Cat 3 - 5
Cat 3 - 1
Cat 3 - 1
Cat 3 - 2

Whereas what I actually need is:

Cat 1 - 12
Cat 2 - 13
Cat 3 - 8

Hope that makes sense!

 

[cotton31]

Works like a charm! Thanks very much!

Your last bit of code was almost what I had, but I was missing the use of Any() and the conditional operator.

Thanks for your help again!

 

[cotton31]

I have another question, which is related!

My whole system revolves around filtering the set of Projects by various properties, so I have a method that applies all the filters and returns a list of Project objects.

This is used in a few places - I want to also use it in what I've been doing above.

As it's a list of Projects, I think I need to Join it to the SubCategories, so I have the following:

db.SubCategories.Join(GetProjects(), subcat => subcat.ProjectCategories.Select(projcat => projcat.ProjectID), proj => proj.ID, (projcat, proj) => new {projcat, proj}).GroupBy(out => out.cs.Category.Description)
                .Select(groupedCat => new
                {
                    CategoryName = groupedCat.Key,
                    NumberOfProjects = groupedCat.Count()
                }
            );

However I'm getting an error:

The type arguments for method 'System.Linq.Enumerable.Join<TOuter,TInner,TKey,TResult>(System.Collections.Generic.IEnumerable<TOuter>, System.Collections.Generic.IEnumerable<TInner>, System.Func<TOuter,TKey>, System.Func<TInner,TKey>, System.Func<TOuter,TInner,TResult>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

This bit subcat.ProjectCategories.Select(projcat => projcat.ProjectID) seems to be causing the error, and I think it's because I'm having go through another entity to get the ID I need (e.g. if I could just do subcat.ProjectID, then it would work fine.)

Am I even on the right track? Any ideas?

Thanks!
Matt