Cast IQueryable to IQueryable for EntityFramework

Question

I'm trying to convert an IQueryable to IQueryable to allow me to take advantage of the dynamic filtering offered by Linq in the EntityFramework context. I'm trying to adhere to repository pattern and not expose anything EntityFramework related outside of the repository.

The code...

My Domain Model

public class Invoice
{
    public Invoice()
    {
        InvoiceItems = new HashSet<InvoiceItem>();
    }

    public Guid Id { get; set; }
    public string Number { get; set; }
    public DateTime UtcDate { get; set; }
    public DateTime? UtcDueDate { get; set; }
    public decimal Amount { get; set; }
    public bool IsPublished { get; set; }
    public bool IsPosted { get; set; }
    public DateTime UtcCreatedAt { get; set; }
    public DateTime? UtcUpdatedAt { get; set; }
    public DateTime? UtcDeletedAt { get; set; }

    public ICollection<InvoiceItem> InvoiceItems { get; set; }
}

My Entity Model

public class InvoiceModel
{
    public InvoiceModel()
    {
        InvoiceItems = new HashSet<InvoiceItemModel>();
    }

    public int Id { get; set; }
    public Guid PublicId { get; set; }
    public string Number { get; set; }
    public DateTime UtcDate { get; set; }
    public DateTime? UtcDueDate { get; set; }
    public decimal Amount { get; set; }
    public bool IsPublished { get; set; }
    public bool IsPosted { get; set; }
    public DateTime UtcCreatedAt { get; set; }
    public DateTime? UtcUpdatedAt { get; set; }
    public DateTime? UtcDeletedAt { get; set; }

    public ICollection<InvoiceItemModel> InvoiceItems { get; set; }
}

My Repository

public class SampleRepository : ISampleRepository
{
    private readonly IDbContextFactory<FinancialsSqlDbContext> _factory;

    public SampleRepository(IDbContextFactory<FinancialsSqlDbContext> factory)
    {
        _factory = factory;
    }

    #region Entity Mapping Methods
    private Invoice FromEntity(InvoiceModel invoice)
        => new Invoice
        {
            Id = invoice.PublicId,
            Number = invoice.Number,
            UtcDate = invoice.UtcDate,
            UtcDueDate = invoice.UtcDueDate,
            Amount = invoice.Amount,
            IsPublished = invoice.IsPublished,
            IsPosted = invoice.IsPosted,
            UtcCreatedAt = invoice.UtcCreatedAt,
            UtcUpdatedAt = invoice.UtcUpdatedAt,
            UtcDeletedAt = invoice.UtcDeletedAt
        };

    private InvoiceModel ToAddEntity(Invoice invoice)
        => new InvoiceModel
        {
            Number = invoice.Number,
            UtcDate = invoice.UtcDate,
            UtcDueDate = invoice.UtcDueDate,
            Amount = invoice.Amount,
            IsPublished = invoice.IsPublished,
            IsPosted = invoice.IsPosted,
            UtcCreatedAt = DateTime.UtcNow
        };

    private InvoiceModel ToUpdateEntity(InvoiceModel model, Invoice invoice)
        => new InvoiceModel
        {
            Id = model.Id,
            PublicId = model.PublicId,
            Number = invoice.Number,
            UtcDate = invoice.UtcDate,
            UtcDueDate = invoice.UtcDueDate,
            Amount = invoice.Amount,
            IsPublished = invoice.IsPublished,
            IsPosted = invoice.IsPosted,
            UtcCreatedAt = model.UtcCreatedAt,
            UtcUpdatedAt = DateTime.UtcNow
        };

    private InvoiceModel ToRemoveEntity(InvoiceModel model, Invoice invoice)
        => new InvoiceModel
        {
            Id = model.Id,
            PublicId = model.PublicId,
            Number = invoice.Number,
            UtcDate = invoice.UtcDate,
            UtcDueDate = invoice.UtcDueDate,
            Amount = invoice.Amount,
            IsPublished = invoice.IsPublished,
            IsPosted = invoice.IsPosted,
            UtcCreatedAt = model.UtcCreatedAt,
            UtcUpdatedAt = model.UtcUpdatedAt
        };
    #endregion

    public async Task<List<Invoice>> ListAsync(Func<IQueryable<Invoice>, IQueryable<Invoice>> predicate = null)
    {
        using (var context = _factory.CreateDbContext())
        {
            var query = predicate != null ? predicate.Invoke(new List<Invoice>().AsQueryable()).ToDTO<Invoice, InvoiceModel>() : context.Invoices.AsQueryable();

            var result = await query.ToListAsync();

            return result.Select(FromEntity).ToList();
        }
    }
}

The method that throws is

public async Task<List<Invoice>> ListAsync(Func<IQueryable<Invoice>, IQueryable<Invoice>> predicate = null)
{
    using (var context = _factory.CreateDbContext())
    {
        var query = predicate != null ? predicate.Invoke(new List<Invoice>().AsQueryable()).ToDTO<Invoice, InvoiceModel>() : context.Invoices.AsQueryable();

        var result = await query.ToListAsync();

        return result.Select(FromEntity).ToList();
    }
}

My latest attempt is from this answer Cast IQueryable<EntityObject> to IQueryable<Specific> which doesn't work when I call ToListAsync() as it throws the following exception:

System.InvalidOperationException: The source 'IQueryable' doesn't implement 'IAsyncEnumerable<Financials.Server.Data.Sql.Models.InvoiceModel>'. Only sources that implement 'IAsyncEnumerable' can be used for Entity Framework asynchronous operations.
  at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.AsAsyncEnumerable[TSource](IQueryable`1 source)
  at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
  at Financials.Server.Data.Sql.Repositories.SampleRepository.ListAsync(Func`2 predicate)

I am not quite sure the best way to go about doing this or if it is even possible.

Side Note - Do not fall into this trap. You are creating a lot of extra an unnecessary work and also making your code base more difficult to use. A DbContext is an implementation of a Repository Pattern. There is no good reason to wrap this again in your own implementation and your abstraction shown offers no benefit. If you do this I can promise you with certainty that you will come to regret your decision. — Igor, May 10 '22 at 14:18
Putting a low-level repository over a high-level DbContext is an ugly *anti*pattern. The phrase `convert an IQueryable to IQueryable to allow me to take advantage of the dynamic filtering offered by Linq in the EntityFramework context.` is unclear too. A DbContext is a multi-entity Unit-of-Work, a DbSet is the single-entity Repository class that can be used for querying. None of them is an IQueryable and don't offer any kind of filtering, dynamic or otherwise — Panagiotis Kanavos, May 10 '22 at 14:19
Do note that I am not talking about returning domain objects from certain calls where you return or accept a DTO which has aggregate information or a subset of information. That is perfectly fine and often used but this is usually done at a boundry level in your application (like user input or output). — Igor, May 10 '22 at 14:19
LINQ is a generic query language. ORMs like EF Core can use that language instead of eg HQL (Hibernate's query language). That query gets translated to SQL by EF and the underlying database provider. There's nothing inherently dynamic or undynamic, the operators and expressions are *generic* — Panagiotis Kanavos, May 10 '22 at 14:21
Furthermore, the "repository" (they aren't) classes are littered with mapping calls that map one DTO to another. Why? First of all, your classes are identical. You gain nothing by mapping. Second, LINQ's `Select` can be used to map query results directly to the desired DTO without first going through the entity object. You can use AutoMapper to map from one DTO to another in a single line, or even use its EF support to generate the Select clause based on the mappings — Panagiotis Kanavos, May 10 '22 at 14:25
Finally, what's the point of `ListAsync`, the method that throws? Filtering is provided by LINQ's `Where` - the expression is mapped to a WHERE clause in SQL, not by executing any predicates. Worse, the method is loading entire objects in memory and *then* mapping them with Select - why not use Select from the start to only load what's needed? — Panagiotis Kanavos, May 10 '22 at 14:29
@PanagiotisKanavos the reason I am putting a repository above the DbContext is that i always receive a domain model back to the service layer instead of a data model. If this is considered anti-pattern, then please show me an example of an acceptable one. I was under the impression that the benefit of doing it this was was that the service layer was always only dealing with domain objects and never anything from the DAL. That way we could change the implementation to any other kind of sql provider and never worry about what the repository is returning/interacting with. — Jason Ayer, May 10 '22 at 14:34
In short, all these problems are caused by trying to "improve" EF if not reinvent it. The Repository pattern in DDD does **not** deal with individual entities, it loads entire aggregates (graphs of objects). That is already implemented by EF Core. A meaningful accounting repository would load a customer's invoices complete with invoice detail lines by using EF directly but only returning the invoices to the caller. — Panagiotis Kanavos, May 10 '22 at 14:34

score 0 · Answer 1 · answered May 16 '22 at 08:03

0

Try AutoMapper. It allow you to Map from One entity to Another and have a multiple ways to work with IQueryable and EF. If you have any question, check out this PoC.

answered May 16 '22 at 08:03

José Ramón Barro

9
1

Cast IQueryable to IQueryable for EntityFramework

1 Answers1