Reuse Linq to SQL code with entityframework

Question

I am currently doing some refactor on code that makes my application very slow. I am pretty far but i am still missing some pieces of the puzzle, i hope you can help me.

I like to reuse some Linq to SQL code inside of my project. This is my way of doing it at this moment:

public DomainAccount GetStandardUserAccount()
{
    return  this.DomainAccounts.Where(da => da.DomainAccountType == DomainAccountType.Standarduser).First() as DomainAccount;
}

var CurrentSituation = _context.Employees.ToList().Where(e => e.GetStandardUserAccount().Username.Contains("test")).ToList();

A small clarification: Every employee has multiple domain accounts where one always is a standarduser(DomainAccountType) domainaccount.

Because Linq can not convert an C# methode to an sqlstatement (Eventho its linq to sql code only) I have to convert the dbset to a list first so i can use the GetStandardUserAccount(). This code is is slow because of this whole dbset conversion. Is there a way i can reuse linq to sql code without turning it in an methode? I have read some threads and this is what I got untill now:

Func<Employee, DomainAccount> GetStandardDomainAccount = x => x.DomainAccounts.FirstOrDefault(d => d.DomainAccountType == DomainAccountType.Standarduser);
var TheGoal = _context.Employees.Where(e => e.GetStandardDomainAccount().Username.Contains("Something")).ToList();

First you need a Func, not an Expression<Func>. Anyway you have to call that delegate, either by calling Invoke or just (). — MakePeaceGreatAgain
– MakePeaceGreatAgain, Commented Dec 5, 2018 at 8:44
1) If this lambdas are being translated into SQL (assume this because of _context variable), then at least you have a problem with the query: string.Contains results into LIKE '%whatever%', which, in turn, does not use indexes. 2) as without following null-checking is a way to NRE. If something must be DomainAccount, then cast it explicitly. If it can be DomainAccount, and its OK, when it is not DomainAccount, then use as with following null-checking. — Dennis
– Dennis, Commented Dec 5, 2018 at 9:08
1) the linq will be translated into sql but string.contains always works for me (see current solution). 2) I am not sure what to do with this feedback. In the C# methode 'as' is used but should i use it in my func<> aswell? — Pieter
– Pieter, Commented Dec 5, 2018 at 9:43
@Pieter I deleted my answer as it is of no use on Linq2Entities. — MakePeaceGreatAgain
– MakePeaceGreatAgain, Commented Dec 5, 2018 at 9:53

Pieter · Accepted Answer · 2019-02-05 12:53:50Z

2

The answer to this question is a bit more complicated than it looks. In order to let linq execute C# code you need to make the function an expression so the in and output will be interperted not as code but as some sort of a meaning. The solution looks like this:

 private Expression<Func<TPeople, bool>> GetDefaultDomainAccount<TPeople>(Func<DomainAccount, bool> f) where TPeople : Person
        {
            return (a) => f(a.DomainAccounts.FirstOrDefault(d => d.DomainAccountType == DomainAccountType.Standarduser));
        }

Now the code can be called uppon like this:

    public IQueryable<TPeople> GetPeopleByUsername<TPeople>(string username) where TPeople : Person
    {
        GetPeople<TPeople>().Where(GetDefaultDomainAccount<TPeople>(d => d.Username == username));
        return people;
    }

instead of this:

        public IQueryable<TPeople> GetPeopleByUsername<TPeople>(string username) where TPeople : Person
    {
        username = username.ToUpper();
        var people = GetPeople<TPeople>()
            .Where(a => a.DomainAccounts.FirstOrDefault(d => d.DomainAccountType == DomainAccountType.Standarduser).Username.ToUpper().Contains(username));

        return people;
    }

answered Feb 5, 2019 at 12:53

Pieter

4705 silver badges16 bronze badges

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

1 Comment

Iti Tyagi Over a year ago

This is helpful

Collectives™ on Stack Overflow

Reuse Linq to SQL code with entityframework

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related