approach for DB access with EF 6?

Question

Looking for approach for DB access with EF 6. i am using .net MVC 5 and i have this code:

    public IEnumerable<string> GetSPSites()
    {
        List<string> spSites = new List<string>();

        using (var context = new DevPortalDBContext())
        {
            var sites = context.Spsites;
            foreach(var item in sites)
            {
                spSites.Add(item.Url);
            }
        }

        return spSites;

    }

But i guess this approach for accessing the db every time is not so good. i would like to know if there is a better way or something like dependency injection that i can do in the constructor. please let me know, Thank you ! (* new to .Net *)

my assumption is that 100 users will use this code at parallels.

You should consider looking into Linq/IQueryable and async/await. In your case it would look something like this: "return await context.Spsites.Select(x=>x.Url).ToListAsync();" — JKamsker, Jun 04 '20 at 08:05
_"What is the best ..."_ is a good indicator for an "opinion based" question. Which is likely to get closed for that exact reason. — Fildor, Jun 04 '20 at 08:07
You mention, that you assume 100 users using this code in parallel. So if you really talk about 100 users doing the exact same thing within a few milliseconds timespan AND if the list doesn't change often and some delay in serving changes to users is acceptable, then you should think about caching. — grek40, Jun 04 '20 at 09:59

JKamsker · Accepted Answer · 2020-06-04T08:20:28.260

If you are for performance, your code isn't exactly that.

But why is it?
Well, good question.
First of all, EF is loading all items from the whole table and we are really only using Spsites.Url. So what could we do to improve this behavior? We could use Linq (But you should be sure that you are using IQueryable instead of IEnumerable which could come at a cost)

public IEnumerable<string> GetSPSites()
{
    return context.Spsites.Select(x => x.Url).ToList();
}

Ok, now that we are just loading the data that is required, could we do something else to improve throughput? Yes! We could use async code using async/await.

public async Task<IEnumerable<string>> GetSPSites()
{
    return await context.Spsites.Select(x => x.Url).ToListAsync();
}

But what does this magical await do?
Well, on a low level, you are talking to a server over the wires. What synchronous code does, is waiting for the response. This means, that the current thread sleeps until the response from the sql server comes in, and we dont want to do this, because threads are really expensive in terms of performance and throughput. await changes your code behind the scenes in that way, that your current thread can do other workloads (like serving other requests) while waiting for a response from the sql server.

But be careful: There are things you should be aware of. One of those is, that you have to switch to async programming all the way to the "root" of your program to improve the performance of your program.

Thank you! its really helped. also do you know if there is a way to make an IDbContext interface for DI ? — maor yadin, Jun 04 '20 at 08:39
Depends on which DI framework you are using. There is a extension for Microsoft's IServiceCollection, named "AddDbContext". For unity its something like container.RegisterType(new PerResolveLifetimeManager(), new InjectionFactory(x => new DevPortalDBContext())); — JKamsker, Jun 04 '20 at 09:02
https://stackoverflow.com/questions/29560294/inject-dbcontext-with-autofac/29560994 — JKamsker, Jun 04 '20 at 09:33

approach for DB access with EF 6?

1 Answers1