2

My first time using the Entity Framework so I'm not sure if I'm doing this right.

I have 4 tables:

CustomerOrder
------------
ID, StaffID, DeptID, Status, other columns...

Staff
------------
StaffID, other columns...

StaffDept
------------
StaffID, DeptID - only 2 fields

Dept
------------
DeptID, other columns...

A staff member can belong to multiple departments. The StaffDept table is just for storing this many-to-many relationship.

I need to retrieve a combination of:

  • all Customer Orders that don't have a Status of "In Progress"
  • any extra records for the current Staff member with a Status of "In Progress"
  • any extra records with a Status of "In Progress" where the Staff member is a member of the same department as the Customer Order.

for example if I have the following data:

Staff
-----
1, Mr X, ...
2, Mr Y, ...

Dept
-----
1, Sales, ...
2, Marketing, ...

StaffDept
-----
1, 1
1, 2
2, 2

CustomerOrder
-----
1, 1, 1, In Progress, ...
2, 1, 1, Completed, ...
3, 2, 2, In Progress, ...
4, 2, NULL, In Progress, ...

I would expect if the current user was #1 they would see customer orders 1,2,3. User #2 would see 2,3,4.

Here is the code I have so far:

from co in CustomerOrders
where co.Status != "In Progress" 
  || co.StaffID == @CurrentStaffID
  || (co.StaffID != @CurrentStaffID 
      && co.DeptID!= null 
      && Staffs.Where(x => x.StaffID == @CurrentStaffID).FirstOrDefault().Depts.Any(x => x.DeptID== co.DeptID))
select new CustomerOrderObject
{
    Description = co.Description,
    Amount = co.Amount,
    ...
}

which works, but Resharper complains that the FirstOrDefault() part will throw a NULL exception at runtime. I've tested with a User of #3 (which doesn't exist) in Linqpad and it doesn't throw an error - it returns just record 2 which is what I would expect. Resharper wants me to pull the Staffs.Where(x => x.StaffID == 3).FirstOrDefault() out into a separate query run before the query above, but I'm thinking that will make it slower? I'm really not sure that any of this query is the fastest way to get the data as I'm new to linq to entities, I've been using linq-to-sql. Any advice would be greatly appreciated - I'm not even sure how to correctly describe the type of join I'm trying to do.

JumpingJezza
  • 5,498
  • 11
  • 67
  • 106

1 Answers1

2

Since you are simply retrieving a member of the Staff by its primary key, and you know there will be exactly one staff member by that ID, you should replace FirstOrDefault with Single.

Staffs.Single(x => x.StaffID == @CurrentStaffID).Depts.Any(x => x.DeptID== co.DeptID))

Edit 1:

Perhaps you can fold your query a bit:

StaffDept.Any(sd => sd.StaffId == @CurrentStaffID && sd.DeptID== co.DeptID)

Edit 2:

Staff.Any(x => x.StaffID == @CurrentStaffID && x.Depts.Any(d => d.DeptID == co.DeptID)
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • well that fixed resharper complaining. The reason I didnt do it earlier is that Linqpad throws an error saying "NotSupportedException: The methods 'Single' and 'SingleOrDefault' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead." Is this the best way to do this join in linq to entities though? – JumpingJezza Dec 08 '11 at 02:32
  • @JumpingJezza See if the second version of the query that goes straight for `StaffDept` would work out better. – Sergey Kalinichenko Dec 08 '11 at 02:42
  • thats the odd thing. In my entity data model where I imported the database it has cut out the `StaffDept` table and just put a many-to-many join there. I assume thats some smart enity framework stuff – JumpingJezza Dec 08 '11 at 03:08
  • @JumpingJezza Oops, I did not realize that your junction table does not have a primary key (if it did, EF would keep it). Let's try another edit (in a minute)... – Sergey Kalinichenko Dec 08 '11 at 03:22
  • that keeps linqpad and resharper both happy :) – JumpingJezza Dec 08 '11 at 04:48
  • I should have mentioned I changed the Contains to Any – JumpingJezza Dec 08 '11 at 04:56
  • @JumpingJezza You are right, it should be `Any`. I edited the answer. – Sergey Kalinichenko Dec 08 '11 at 05:10