Creating DbContext instance inside LINQ

Question

Just a quick question I thought I will ask to confirm I'm not introducing unnecessary trouble to my code. Is it ok to create a IDbContextFactory object directly inside LINQ, which is inside viewmodel's aggregate property like so:

public IEnumerable<ExpenseTotal> _totalsByExpenseType;
public IEnumerable<ExpenseTotal> TotalsByExpenseType
{
    get 
    {
        return (from a in _context.Expenses.Local 
                    join b in _context.ExpenseTypes.Local 
                    on a.ExpenseTypeID equals b.ExpenseTypeID 
                    join c in _contextFactory.CreateDbContext().ExpenseProjections
                    on a.ExpenseTypeID equals c.ExpenseTypeID
                where a.DateCreated >= LastSalaryDate 
                group a by new { a.ExpenseTypeID, b.ExpenseTypeName, c.ProjectedCost } into grp
                select new ExpenseTotal
                {
                    ExpenseTypeID = grp.Key.ExpenseTypeID,
                    ExpenseTypeName = grp.Key.ExpenseTypeName,
                    ActualCost = grp.Sum(x => x.ActualCost), 
                    ProjectedCost = grp.Key.ProjectedCost
                });
    }
}

More specifically I'm asking if _contextFactory object will be disposed of properly this way? A quick search lead me to this article, which, in my understanding, suggests it's an ok approach to use?

EDIT1: The DbContextFactory is created via IServiceCollection.AddDbContextFactory<T> with ServiceLifetime.Transient option.

EDIT2: It's a WPF app with EF Core 6. The issue is (I think) that I'm using DbContextOptionsBuilder.UseLazyLoadingProxies() for my DbContext and that results in data being returned only when they are queried. So I have the main context, which is _context and then I'm calling the rest via _contextFactory when I need it using using statement, which disposes the temporary context properly.

Judging from the code and name of the variables alone, I would say no, this will not properly dispose of the created DbContext. However, if the actual implementation of `CreateDbContext()` returns a singleton or scoped instance, then it may be fine, however the verb "Create" leads me to believe this is not the case. — Matthew, Nov 18 '22 at 14:07
Thank you, @Matthew. In this case the `DbContextFactory` is created via `IServiceCollection.AddDbContextFactory` with `ServiceLifetime.Transient` option. — Donatas, Nov 18 '22 at 14:25
I would just inject both contexts in the constructor and call it a day! — GH DevOps, Nov 18 '22 at 14:33
Are you using Entity Framework? If not, why not, it would save a lot of effort. Specifically, in cases like this, you could add navigation properties to your models, then you wouldn't need to `join` anything, Linq would do it all for you. Also, you could use the one context at the root of your query. — Avrohom Yisroel, Nov 18 '22 at 14:36
Thank you, @AvrohomYisroel. Yes, it's a WPF app with EF 6. I feel I should have added more information to the question, so my apologies. The issue is (I think) that I'm using `DbContextOptionsBuilder.UseLazyLoadingProxies()` for my DbContext and that results in data being returned only when they are queried. So I have the main context, which is `_context` and then I'm calling the rest via `_contextFactory` when I need it using `using` statement, which disposes the temporary context properly. — Donatas, Nov 18 '22 at 14:42
You don't need a DbContext inside LINQ. That's simply not how EF works. A DbContext isn't a database connection, it's a Unit-of-Work and multi-entity repository. You can only work with a single DbContext entities. The LINQ query you write isn't executed. It's converted to SQL and executed, then the results are mapped to whatever is returned by the LINQ query — Panagiotis Kanavos, Nov 18 '22 at 14:48
Using JOINS in LINQ like this suggests the DbContext model is wrong and lacks relations between entities. It's *EF Core's* job to generate queries from the relations and navigation properties. You should be able to write `from expense in ctx.Expenses where expense.ExpenseType.ID=42 && expense.ExpenseType.Projections.Any(p=>p.DateCreated > SalaryDate) ...`. EF will generate a SQL query with joins between Expenses, ExpenseTypes, ExpenseProjections — Panagiotis Kanavos, Nov 18 '22 at 14:51
Thank you @PanagiotisKanavos. I will probably need to review my design. Up to now I was simply following the best MVVM practices and it all seems to work neatly. EF > DbContext > Data models > Data models VMs > VMs for Data models VMs > The Main VM combined of all VMs > The View. I just didn't want to write additional data load logic into the main DBContext, if I can achieve the same with a single short line of code :) — Donatas, Nov 18 '22 at 14:58
MVVM says nothing about how the ORM is used. Those aren't ViewModels. Entity relations aren't logic, they're the reason ORMs exist in the first place. The acronym ORM stands for Object Relational Mapping. It's meant to map tables to object graphs *with* their relations, not act as Embedded SQL. It's the opposite - ORMs try to give the impression you're working with in-memory objects instead of databases. That works up to a point — Panagiotis Kanavos, Nov 18 '22 at 15:00
If you have to write so much code, why not write a parameterized SQL query and execute it with Dapper? LINQ is only getting in the way if you have to write so much code — Panagiotis Kanavos, Nov 18 '22 at 15:03
Even in a stateful application like WPF you should avoid using a long-lived context. What you do here indicates that you already experience context bloat. In my experience, in such applications, it's very useful to pretend that the application acts like a web application. That said, as a quick fix, you can use the same `_context` inside the query and add `AsNoTracking`. — Gert Arnold, Nov 18 '22 at 15:05
You may well be right @PanagiotisKanavos. I am indeed able to use the query you suggested, but the issue is that for some reason (using lazy loading proxy perhaps?) that model doesn't return any data at that point. Strangely though, if I check the object via debugger - it has all the data in all the models loaded, which implies that I should be able to use the query you suggested. Instead, I have to call these data explicitly for some reason, if they were not 'touched' (for the lack of better word) during the initial data load via constructor. — Donatas, Nov 18 '22 at 15:09
@Donatas Navigation properties are not populated in EF Core by default, but you can force them to be added to the query by using `.Include(x => x.NavProperty)` on the query source. E.g. see https://stackoverflow.com/a/6776439/625594 plus comments. — Sergey Kudriavtsev, Nov 18 '22 at 15:17
Thank you @SergeyKudriavtsev. Your comment helped me to realise the solution to my initial issue with missing data. — Donatas, Nov 18 '22 at 17:18

score 1 · Answer 1 · answered Nov 18 '22 at 17:14

Ok, so I've done what @SergeyKudriavtsev suggested in his comment, and made sure that this particular navigation property is being included in the initial context data load, which loads 2 main VMs for that screen.

The re-factured code is this, so no additional DbContext loads, although my initial question still stands :)

Model:

public class ExpenseType
{
    [...]
    public virtual ICollection<ExpenseProjection> ExpenseProjections { get; private set; } = new ObservableCollection<ExpenseProjection>();
}

ViewModel:

private async Task LoadExpenseTypes()
{
    ExpenseTypes = new ObservableCollection<ExpenseType>();

    foreach (var expenseType in _context.ExpenseTypes.Include(x => x.ExpenseProjections))
    {
        ExpenseTypes.Add(expenseType);
    }
}

public IEnumerable<ExpenseTotal> _totalsByExpenseType;
public IEnumerable<ExpenseTotal> TotalsByExpenseType
{
    get 
    {
        return (from a in _context.Expenses.Local
                    join b in _context.ExpenseTypes.Local
                    on a.ExpenseTypeID equals b.ExpenseTypeID
                    join c in _context.ExpenseProjections.Local
                    on a.ExpenseTypeID equals c.ExpenseTypeID
                where a.DateCreated >= LastSalaryDate
                group a by new { a.ExpenseTypeID, b.ExpenseTypeName, c.ProjectedCost } into grp
                select new ExpenseTotal
                {
                    ExpenseTypeID = grp.Key.ExpenseTypeID,
                    ExpenseTypeName = grp.Key.ExpenseTypeName,
                    ActualCost = grp.Sum(x => x.ActualCost),
                    ProjectedCost = grp.Key.ProjectedCost
                });
    }
}

Creating DbContext instance inside LINQ

1 Answers1