CODEWITHSUNDEEP
c#linqentity-framework-6
Karen Avdalyan
Karen Avdalyan

Reputation: 392

How to reuse where clause in EF

I want to make a query using EF and my query looks like the following

var users = MyDbc.AspNetUsers.Where
            (
                  d => 
                  (
                      d.UserPlans.Where(m => m.IsActive == 1).FirstOrDefault().PlanId == 2
                      ||
                      (d.UserPlans.Where(m => m.IsActive == 1).FirstOrDefault().PlanId == 3 
                        && d.UserGroups.FirstOrDefault().Group.AdminId == d.Id)
                  )
            );

How can I avoid querying UserPlans twice? in SQL I would write the following

SELECT * FROM AspNetUsers U INNER JOIN UserPlan UP ON U.Id = UP.UserId 
WHERE UP.IsActive = 1
AND (
PlanId=2 OR (PlanId=3 AND EXISTS(SELECT 1 FROM [Group] WHERE AdminId=U.Id ))
)

Upvotes: 4

Views: 382

Answers (3)

eocron
eocron

Reputation: 7526

Just use built-in query syntax:

var users = from user in MyDbc.AspNetUsers
            from plan in user.UserPlans.Where(m=>m.IsActive == 1)
            where plan.PlanId == 2 || plan.PlanId == 3 && ...

It is more agile in terms of selecting query time variables. And it is MUCH more readable when your query goes large. For simple queries I personally still use pure LINQ extensions.

PS

As @juharr mentioned in comment, your query will probably be optimized by SQL provider, so you are good with performance, but readability and fault-resistance of your query is indeed poor.

Upvotes: 4

H.Mikhaeljan
H.Mikhaeljan

Reputation: 813

I think your looking for this. You can just do both checks straight in the first where.

d.UserPlans.Where(m => m.IsActive == 1 && 
(m.PlanId == 2 || (m.PlandId == 3 &&
d.UserGroups.FirstOrDefault().Group.AdminId == d.I)))

Upvotes: 1

Xaphas
Xaphas

Reputation: 519

I would probably pack this into a view or stored procedure (if you need dynamic ids) and load that separately.

CREATE VIEW Users
AS   
SELECT * FROM AspNetUsers U INNER JOIN UserPlan UP ON U.Id = UP.UserId 
WHERE UP.IsActive = 1
AND (
PlanId=2 OR (PlanId=3 AND EXISTS(SELECT 1 FROM [Group] WHERE AdminId=U.Id ))
)
GO

and then call it in c#...

var users = MyDbc.Users.ToList();

... like that.

Upvotes: 1

Related Questions