LINQ Help

cotton31 · 30 Jul 2014 at 11:28

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

Code:

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:

Code:

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!

Gambisk · 30 Jul 2014 at 12:09

when in doubt stack overflow it

http://stackoverflow.com/questions/7325278/group-by-in-linq

GravyMonster · 30 Jul 2014 at 13:14

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.

Thrash · 30 Jul 2014 at 13:19

This is a handy page for linq examples: http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

cotton31 · 30 Jul 2014 at 16:00

Spunkey said:
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?

GravyMonster · 30 Jul 2014 at 16:05

LINQ is LINQ, irrespective of the framework you use.

If you have the ability to use SQL for anything, do so as it'll be much quicker. I'm not saying LINQ isn't without it's uses and incredibly handy, but there are better ways of munging data about - especially when talking to a database.

Haircut · 30 Jul 2014 at 16:20

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

What exactly are you using? Is it LINQ to SQL or LINQ to EF as they are two very different beasts?

As for performance, I see no reason why a well written query shouldn't be pretty close to the performance of a SP. Spunkey, why do you think it would be an order of magnitude slower?

cotton31 · 30 Jul 2014 at 16:20

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 · 30 Jul 2014 at 16:21

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

xirokx · 31 Jul 2014 at 09:31

its not as easy to just give you the code as there are few things you need to understand prior...even though its a entityframework 4.0 the concept is very similar...this is a short and simple example for CRUD operations using entityframework 4.0 in my opinion its well explained..

https://www.youtube.com/watch?v=BS6IKdUd2V8&list=FLK7bxRIwIEgEcJP7EXQMvtg&index=7

EntityFramework is very powerful and lovely to work with

cotton31 · 31 Jul 2014 at 17:36

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:

Code:

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!

xirokx · 2 Aug 2014 at 02:12

yes your example is correct, however I am not sure you can load straight into a list so what I would probably do is declare a list and then load all records from that table into the list as follows:

List<Person> Px = new List<Person>();

Px= entities.Projects.ToList();

Now you can access all the properties of the object as follows:

string name = "david";
int fund = 100000;

var existsInProjects tdable = Projects.Where(x => x.ProjectName == name).Where(x => x.ProjectFund == fund);

(this example would return any records in the projects table if they have the name "david" and a project fund of 1000000;

Alternatively you could try what you suggested but instead of loading directly into a list I would do the below:

var x = db.Projects.Select(p=>p.A field in the table).toList();

the above line of code, loads all the records from a single table in the database to variable x......I believe

alternatively you can also do:

DateTime checkTime = DateTime.Now;

bool timeMatch = db.Projects.Any(x => x.ScheduledTime == checkTime);
(this example checks if the values in the field "scheduledTime" from the Projects table matches the "checkTime" variable, and returns a true / false as its a boolean..)

hope that helps....

LINQ is powerful, there are so many ways you can do 1 thing, its not a one size fits all Im afraid.

I hope that helps and doesnt confuse you...

Haircut · 2 Aug 2014 at 14:16

xirokx said:
what I would probably do is declare a list and then load all records from that table into the list as follows:

List<Person> Px = new List<Person>();

Px= entities.Projects.ToList();

Now you can access all the properties of the object as follows:

string name = "david";
int fund = 100000;

var existsInProjects tdable = Projects.Where(x => x.ProjectName == name).Where(x => x.ProjectFund == fund);

(this example would return any records in the projects table if they have the name "david" and a project fund of 1000000;

This is almost certainly what you don't want to do.
That will query all the records from the database and return them to the client, where you're doing the filtering in memory using LINQ to Objects.

The beauty of LINQ is that it can be turned into an expression tree and the SQL generated from that expression tree, which means you're not bringing back a load of stuff from the database that you don't need.
(Some detail on that here: http://jpreecedev.com/2013/11/24/in-the-spotlight-demystifying-iqueryable-entity-framework-6/)

The code that the OP posted in his last post should work perfectly well.
The issue seems to be that the domain objects don't have the required properties on to do the query.

I'm no expert with EF, especially database first stuff, but as far as I understand it then if you have a foreign key between your tables in the db you should get navigation properties in the domain objects to navigate the 1 to many and many to 1 relationships.

cotton31, do you have the requisite foreign keys in your db to generate the navigation properties?
What do the generated domain objects look like in your case?

xirokx · 2 Aug 2014 at 19:24

That will query all the records from the database and return them to the client, where you're doing the filtering in memory using LINQ to Objects.

Really? even though the below code loads all records from database into memory so you can filter through each record to perform checking or comparisons:

List<Person> Px = new List<Person>();

Px= entities.Projects.ToList();

I'm no expert with EF, especially database first stuff,

yet you say:

This is almost certainly what you don't want to do.

so I wonder, if you are no expert how can you be certain what I posted is not what you do?

cotton31, do you have the requisite foreign keys in your db to generate the navigation properties?
What do the generated domain objects look like in your case?

now you are confusing the bloke, this makes no difference at all.

The only thing that comes into play is if you have composite key (2 primary keys) then when loading data from database into memory then you have to use both primary key fields....however this bears no relevance whatsoever on the simple and straight forward action the OP wants to perform.

Finally whilst this link speaks about LINQ

http://jpreecedev.com/2013/11/24/in-...y-framework-6/)

It is not required in the OPs case, as he wants to merely perform simple commands he doesnt need to go down the IQUERYABLE route....

Keep It Simple Stupid = K.I.S.S.

Thanks

cotton31 · 2 Aug 2014 at 20:15

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!

Haircut · 4 Aug 2014 at 19:08

xirokx said:
Really? even though the below code loads all records from database into memory so you can filter through each record to perform checking or comparisons:

so I wonder, if you are no expert how can you be certain what I posted is not what you do?

When I said I wasn't an expert in EF stuff I meant about how EF generates code and general usage of it.
The bits that I posted were about general LINQ query providers, which I do know a bit about.

I was simply commenting on the fact that this:

Code:

var projects = db.Projects.ToList();
var filtered = projects.Where(x => somecriteria);

is very different from doing this:

Code:

var filtered = db.Projects.Where(x => somecriteria).ToList();

The first will do a database select from all records in the table and filter them in memory.
The second will do a database select with a where clause on your criteria, which if your criteria columns are indexed on the DB, will provide big benefits.

That's because EF uses the IQueryable stuff internally to translate the LINQ where clause into a SQL where clause.
I posted the link mostly in case anyone was interested in reading some more about it.

I wholeheartedly agree with KISS, but you should be aware of the technologies you're using and how to use them effectively.

That said, all of this seems tangential to the OP's actual problem, which I suspect is related to the DB not having the right foreign keys but that's the area I'm not an expert in

philldutton · 4 Aug 2014 at 19:16

Can you post the class definitions you're using? Also, are you using Code First, Model First etc...

What you're trying to do should be straight forward, but it depends how your classes have been defined.

cotton31 · 4 Aug 2014 at 20:30

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)?

davetherave2 · 5 Aug 2014 at 00:15

Apologies in advance if I am getting the wrong end of the stick here. But am I correct in understanding that you want the number of Categories followed by a number of associated projects. (Taken from one of your later posts)

eg Category X has 7 Projects, Category Y has 0 Projects. etc.

If that is the case then surely you would navigate from Category to Projects.

for example

Code:

db.Categories.Select( category=> new CategoryWithNumberOfProjects()
{ 
CategoryName =  category.Name
, NumberOfProjects = (category.Projects.Any()?category.Projects.Count(): 0
}
).ToList();

I'm not an EF man myself as I prefer to use Telerik's DataAccess. I found it so much simpler to use when I first started to use ORMs and moved away from straight ADO.Net.
From what I can tell they do pretty much the same thing in many respects but it just seemed to click with me where EF just seemed a bit too alien when I started out. I am sure if I were to use EF now I could pick it up and transfer the skills I have learnt but I am much happier doing my SQL first and then clicking a button to update my Entities Model based on this. I also find the migrations process a lot easier with DataAccess compared to EF.

cotton31 · 5 Aug 2014 at 12:21

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:

Code:

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:

Code:

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:

Code:

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:

Code:

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

Hope that makes sense!