Making a function to be called within Linq functions

Question

Can I make a function to be called within Linq functions using EF?

for example

int GetEmployeeStatus(string empID)
{
     using (DB = new db())
     {
           var status = db.EmployeeStatus
                        .Where(e => e.EmpID == empID && e.StatusEndDate == null)
                        .Select(e => e.Status)
                        .SingleOrDefault();
           return status;
     }
}

Now is there a way to use the function above anywhere in my applciation in some way like this:

    ....
    var empList = db.Employees
                  .Where(e => e.CostCenterID == 123 
                         && GetEmployeeStatus(e.EmpID) == 1);
    ....

I do not want to write the creteria for finding the employee status over and over again, is there a way to do that or something similar in concept?

One more thing, I know the way I write the function up will cause a database trip for every row, I hope there is way to avoid that and just to embed the query within the Linq so it will be called once.

Hope this link helps you. Do let me know. tomasp.net/blog/linq-expand.aspx — DevelopmentIsMyPassion
– DevelopmentIsMyPassion, Commented Feb 12, 2013 at 20:30

delixfe · Accepted Answer · 2013-02-12 20:28:39Z

7

You can use an extension function:

public static class DbContextExtensions 
{
    public static IQueryable<Employee> WhereX(this IQueryable<Employee> queryable, int id)
    {
        return queryable.Where(e => e.CostCenterID == 123);
    }
}

answered Feb 12, 2013 at 20:28

delixfe

2,5011 gold badge23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Abe Miessler Over a year ago

+1, based on his requirement I think you would want to return bool or int though.

user915331 Over a year ago

Nice, will the SQL generated in this function be embedded into the SQL generated from the calling linq expression? in other means one trip to the Database?

Servy Over a year ago

@HaLaBi I highly suggest that you inspect the log of the data context to see for yourself. If you do it properly, yes, it will be one single round trip, but if you don't do it properly, no. Always get in the habit of checking that log file when developing new queries (especially when first working with a new a query provider) as it helps teach you a lot about how to write effective queries.

delixfe Over a year ago

It does not matter if you use an extension function or have the where clause directly on the db context. You can even combine several extension functions. But nevertheless, as @Servy said always ceck if your queries are sent correctly to the database (using extension methods or not).

Mare Infinitus Over a year ago

You suggest looking into DataContext.Log? Most of even good developers cannot judge what is good SQL and what will perform bad.

|

Collectives™ on Stack Overflow

Making a function to be called within Linq functions

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related