C#•3w ago

✅ How to get better at writing EF Queries?

Maybe I just don't really understand things, but I create database tables try to separate things and do all the relationships, and then I start having to do these massive includes like this, and I feel like its just going to cause performance issues

    public async Task<IEnumerable<Category>> GetAllCategoriesTaskListsAsync(string userId, string categoryName)
    {
        return await _context.Categories
            .Include(c => c.TaskLists)
            .ThenInclude(tl => tl.User)
            .Include(c => c.TaskLists)
            .ThenInclude(tl => tl.TaskListItems)
            .Include(c => c.TaskLists)
            .ThenInclude(tl => tl.UserTaskLists)
            .Where(c => c.UserId == userId && c.Name.ToLower() == categoryName.ToLower())
            .ToListAsync();
    }

    public async Task<IEnumerable<Category>> GetAllCategoriesTaskListsAsync(string userId, string categoryName)
    {
        return await _context.Categories
            .Include(c => c.TaskLists)
            .ThenInclude(tl => tl.User)
            .Include(c => c.TaskLists)
            .ThenInclude(tl => tl.TaskListItems)
            .Include(c => c.TaskLists)
            .ThenInclude(tl => tl.UserTaskLists)
            .Where(c => c.UserId == userId && c.Name.ToLower() == categoryName.ToLower())
            .ToListAsync();
    }

Is there a better way to make these queries? I tried doing courses on EF Core itself to learn better but its always just these simple queries and then my personal projects require more complex queries where I start to hit a world of issues. I've managed to refactor the above into this

    public async Task<List<TaskList>> GetAllTaskListsInCategoryAsync(int categoryId)
    {
        return await _context.TaskLists.Where(t => t.CategoryId == categoryId)
            .Include(t => t.TaskListItems)
            .Include(t => t.TaskListAssignments)
            .ThenInclude(tla => tla.User)
            .ToListAsync();
    }

    public async Task<List<TaskList>> GetAllTaskListsInCategoryAsync(int categoryId)
    {
        return await _context.TaskLists.Where(t => t.CategoryId == categoryId)
            .Include(t => t.TaskListItems)
            .Include(t => t.TaskListAssignments)
            .ThenInclude(tla => tla.User)
            .ToListAsync();
    }

But I still feel like repeatedly doing includes causes issues.

41 Replies

mg•3w ago

generally there's no problem with what you're doing. joins are a part of using relational databases and sometimes you have to do a handful of them. modeling your data is a skill and there are definitely more and less efficient ways to model something, but simply having joins isn't wrong

mg•3w ago

be mindful of cartesian explosion and how to mitigate it and you'll be fine

Single vs. Split Queries - EF Core

Translating LINQ queries into single and split SQL queries with Entity Framework Core

mg•3w ago

you can also benchmark different models/queries to see if something will be a performance issue rather than worrying about if it might

CydoOP•3w ago

Ok, so then im just over thinking it, cause i get so hyperfixated on trying to write better queries and im like idk how else i can do it cause i needed data from x,y,z table, and i dont wanna use .Select() and map directly into dto's in my repository layer, i wanna leave that for the service layer

mg•3w ago

it's not to say that you shouldn't worry about efficient modeling and querying, just that you don't need to obsess over it before it becomes a problem then when it does become a problem, you solve it and keep that knowledge for the next time you're modeling data also a repository over an EF context is an antipattern

CydoOP•3w ago

well this is me currently i get hyper fixated on trying to desigh the best database and make good queries thats there are days that i make no progress it feels like lol. im not sure i understand what this means

mg•3w ago

you don't need a repository class in fact it's going to cause you pain

CydoOP•3w ago

public class CategoryRepository : BaseRepository<Category>, ICategoryRepository
{
    public CategoryRepository(AppDbContext context) : base(context)
    {
    }

    public async Task<Category?> GetByNameAsync(string userId, string categoryName)
    {
        return await _context.Categories
            .FirstOrDefaultAsync(c =>
                c.UserId == userId && c.Name.ToLower() == categoryName.ToLower());
    }

public class CategoryRepository : BaseRepository<Category>, ICategoryRepository
{
    public CategoryRepository(AppDbContext context) : base(context)
    {
    }

    public async Task<Category?> GetByNameAsync(string userId, string categoryName)
    {
        return await _context.Categories
            .FirstOrDefaultAsync(c =>
                c.UserId == userId && c.Name.ToLower() == categoryName.ToLower());
    }

so this is pointless?

mg•3w ago

yep

CydoOP•3w ago

get out of town

mg•3w ago

inject your context directly and call FirstOrDefaultAsync() right where you need it

CydoOP•3w ago

this is how im doing things, i learned from doing a metric fuck ton of udemy courses lol

mg•3w ago

if you need to wrap some commonly used functionality so you don't have to keep typing it, you could write a method on the context or an extension method on the DbSet

CydoOP•3w ago

I basically did this guys entire course track path, and some of the stuff i dont agree with but its how ive been building everything. https://www.dotnetmastery.com/home/careerPath

mg•3w ago

clean architecture 🤮 check out $vsa

MODiX•3w ago

* https://www.youtube.com/watch?v=T-EwN9UqRwE * https://codeopinion.com/restructuring-to-a-vertical-slice-architecture/ * https://www.youtube.com/watch?v=ZA2X1gaAhJk * https://www.youtube.com/watch?v=-mGRtMjws_8&t=975s * https://www.youtube.com/watch?v=fnkzI9IxmOk

mg•3w ago

particularly the last video

CydoOP•3w ago

Thats how i feel about it, i hate it i came to C# from strictly Js/Ts so just functions everywhere no classes lol

mg•3w ago

what, you don't love having to stop and have a philosophical debate with yourself every time you create a new file?

CydoOP•3w ago

i have internal wars. then the next day i come back and cchange my mind

mg•3w ago

yeah my first real projects were CA because i thought it was the "right" way and you definitely don't have to rewrite this now but next time give VSA a shot and see how much nicer it is also https://discord.com/channels/143867839282020352/169726586931773440/1334646965379792927 for some extra explanation on why repositories over EF are useless

MODiX•3w ago

Angius

Repositories are usually more generic. A repository would have a GetAll() method, for example, that would either expose an IQueryable or would just fetch the whole database if done incorrectly

Quoted by

<@72080813948153856> from #help-0 (click here)

React with ❌ to remove this embed.

mg•3w ago

(the conversation from that message on)

CydoOP•3w ago

Yeah this is me, everything ive learned so far has been clean architecture so I got in this mentality where its the right way, and it def makes building things take so much longer i feel. This project is still in the early stages, I built a project management app, https://questbound.xyz/ that was def too ambitious for my first .NET app, so i decided to take it down a peg and do a small generic todo app to really cement everything in that ive learned lol

mg•3w ago

damn, took a glance and that looks really nice

CydoOP•3w ago

Thanks, i am definitely not a designer and take forever to make things even look pleasable LOL

mg•3w ago

i also started a "reference todo app" to try to do something simple really well, but inevitably lost interest it's hard to stay motivated when the project is completely uninteresting lol now i'm a bigger fan of the "make it, make it work, make it work better" mindset

CydoOP•3w ago

Even if i lose interest i make point to at least finish my app, ever since i got laid off in August ive felt like a shit developer so im busting my ass lol. I need this mindset, im a make it perfect or dont move on idiot xD

mg•3w ago

perfectionism is a bitch

CydoOP•3w ago

That questbound app, i got sick of and it was getting too much to manage so i cut out a ton of features g ot it in a working state and deployed it. Better to finish something then to leave it incomplete cause of 'Another feature'

mg•3w ago

yeah there's definitely value in just getting something to the point where you can call it done then wrap it up and take what you learned from it into your next project

CydoOP•3w ago

exactly, except first project was MVC this project is minimal api. not sure which i prefer yet but minimal api lets me get shit going quicker at least

mg•3w ago

i use fastendpoints next project i might give the whole immediate platform a go i.e. immediate.handlers, immediate.apis, etc

CydoOP•3w ago

idk what any of those things are lol. i thought i was finally get somewhere, when i was gonna learn design patterns and CQRS and this mediator thing. xD

mg•3w ago

immediate.handlers is a mediatr replacement but it uses source generators instead of reflection

mg•3w ago

https://immediateplatform.dev/

ImmediatePlatform

Libraries for building modern, maintainable .NET applications leveraging the Vertical Slice Architecture and Mediator pattern with no boilerplate. Extensible. Fast. Source Generated. Open Source.

Thalnos•3w ago

there is three kinds of querying, Eager loading which you have done here, Lazy Loading and Explicite Loading. Check out pros and cons of each that might help you when to use which. I've never used explicite loading and dont really know when to use that honestly but for Eager and Lazy loading, the con of Eager loading is loading the whole data into memory, the con of Lazy Loading is that it can result in too many queries - the N + 1 Queries problem. Generally you should favor Eager loading to not run into slow loading time issues with Lazy Loading but if the loading all data into memory all at once becomes an issue then lazy loading could be beneficial. But do not fall into the trap of preemptive optimization, only try to optimize when you really have a performance issue that needs optimization, otherwise you're just wasting time for nothing 🙂 Lazy Loading can help you not load data into memory that isn't needed, but it results in more queries on the DB, so each approach has pros and cons and is good for certain situations

Angius•3w ago

Specifically looking at this code, learn how to use .Select() and prefer it over a bunch of .Include()s

CydoOP•3w ago

I had a select before, where i was selecting what i wanted and directly mapping it into a DTO, but then i was doing that in my repository layer, and i decided against it because all my mappings to DTOs are in the service layer.

Angius•3w ago

You're discovering the issues that repositories cause lol

CydoOP•2w ago

Okay, yeah ik about the types of querying but, im still not at the point where ik when to use which and why etc. I def fall into the preemptive optimization trap, feel like someones gonna come see one of my apps and be like holy this is crap, runs like shit and then move on Oh ive found the issues awhile ago, i just didnt know if i should structure my api's any different till the VSA was mentioned lol Ok, i left this thread open, and i went off to learn more about EF Core and all these little nuances and I have taken one of the queries in my project which was

    public async Task<List<TaskList>> GetAllTaskListsInCategoryAsync(int categoryId)
    {
        return await _context.TaskLists.Where(t => t.CategoryId == categoryId)
            .Include(t => t.TaskListItems)
            .Include(t => t.TaskListAssignments)
            .ThenInclude(tla => tla.User)
            .ToListAsync();
    }

    public async Task<List<TaskList>> GetAllTaskListsInCategoryAsync(int categoryId)
    {
        return await _context.TaskLists.Where(t => t.CategoryId == categoryId)
            .Include(t => t.TaskListItems)
            .Include(t => t.TaskListAssignments)
            .ThenInclude(tla => tla.User)
            .ToListAsync();
    }

and converted it to this

    public async Task<List<TaskListOverview>> GetAllTaskListsInCategoryAsync(int categoryId)
    {
        return await _context.TaskLists
            .Where(t => t.CategoryId == categoryId)
            .Select(tl => new TaskListOverview
            {
                Id = tl.Id,
                Description = tl.Description,
                CreatedAt = tl.CreatedAt,
                UpdatedAt = tl.UpdatedAt,
                Members = tl.TaskListAssignments
                    .Select(tla => new Members
                    {
                        Id = tla.User.Id,
                        Name = tla.User.FirstName + " " + tla.User.LastName,
                    })
                    .ToList(), 
                TotalTasksCount = tl.TaskListItems.Count(), 
                CompletedTasksCount = tl.TaskListItems.Count(q => q.IsCompleted),
                TaskCompletionPercentage = tl.TaskListItems.Count() == 0 
                    ? 0 
                    : (double)tl.TaskListItems.Count(q => q.IsCompleted) / tl.TaskListItems.Count() * 100
            })
            .ToListAsync();
    }

    public async Task<List<TaskListOverview>> GetAllTaskListsInCategoryAsync(int categoryId)
    {
        return await _context.TaskLists
            .Where(t => t.CategoryId == categoryId)
            .Select(tl => new TaskListOverview
            {
                Id = tl.Id,
                Description = tl.Description,
                CreatedAt = tl.CreatedAt,
                UpdatedAt = tl.UpdatedAt,
                Members = tl.TaskListAssignments
                    .Select(tla => new Members
                    {
                        Id = tla.User.Id,
                        Name = tla.User.FirstName + " " + tla.User.LastName,
                    })
                    .ToList(), 
                TotalTasksCount = tl.TaskListItems.Count(), 
                CompletedTasksCount = tl.TaskListItems.Count(q => q.IsCompleted),
                TaskCompletionPercentage = tl.TaskListItems.Count() == 0 
                    ? 0 
                    : (double)tl.TaskListItems.Count(q => q.IsCompleted) / tl.TaskListItems.Count() * 100
            })
            .ToListAsync();
    }

This would be the more performant way to do things now, since I'm only pulling data I need and then this will also allow me to my make mappings easier, since ill just be mapping an object to object 1:1 instead of doing logic like figuring out the TaskCompletionPercentage in my service and then doing a mapping.

Gaming

Programming

✅ How to get better at writing EF Queries?

Did you find this page helpful?