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
– Matthew, Commented Nov 18, 2022 at 14:07
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
– Panagiotis Kanavos, Commented Nov 18, 2022 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
– Panagiotis Kanavos, Commented Nov 18, 2022 at 14:51
@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 stackoverflow.com/a/6776439/625594 plus comments. — Sergii Kudriavtsev
– Sergii Kudriavtsev, Commented Nov 18, 2022 at 15:17
Thank you @SergeyKudriavtsev. Your comment helped me to realise the solution to my initial issue with missing data. — Donatas
– Donatas, Commented Nov 18, 2022 at 17:18

Donatas · Accepted Answer · 2022-11-18 17:14:13Z

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
                });
    }
}

Collectives™ on Stack Overflow

Creating DbContext instance inside LINQ

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related