I have these models.

public class Ticket
{
    public int Id {get;set;}
    public List<History> History {get;set;}
}

public class History
{
    public Employee Employee {get;set;}
    public string Comments {get;set;}
    public DateTime Timestamp {get;set;}
}

Employee is just a class with an int Id and string Name.

What I had in mind is that I needed to allow the users to query Tickets by a computed property in Tickets, such as

public Employee LatestEmployee
{
    get => History.MaxBy(x=>x.Timestamp).Employee;
}

From the user, I get the name of the queried property/column, the search term and whether the user wants it to contain, start with or be equal to, and I'm using dynamic LINQ, so I can concatenate multiple queries as the user applies further filters. Basically I get a JSON like this:

{
    "column":"Employee",
    "query":"Batman",
    "type":"equals"
}

Which is converted to a Dynamic LINQ "Employee == Batman".

But on the database itself, it still creates an EmployeeId column with a foreign key, so I need to fill it with a random (existing, because of the FK) value.

I, then, query it as

var filteredListQuery = 
_context.Tickets.Include(x=>x.History)
    .ThenInclude(y=>y.Employee)
.Where("Employee.Name == \"Batman\"");

Following that, I must return a JSON with information on the tickets to display. So I try

var filteredListQueryDTO = filteredListQuery
.Select(x=>new TicketDTO()
{
    Id = x.Id.ToString(),
    Employee = x.Employee.Name,
    Timestamp = x.Timestamp.ToString("g")
}).ToListAsync();

But then it reads the employee saved on the unnecesary column, rather than using the computed property. And if I try Employee = x.History.MaxBy(x=>x.Timestamp).Employee.Name I get a LINQ expression could not be translated.

It does work, though, if I, as per the linked KB article on the exception, first call ToList() on the filteredQueryList():

var filteredListQueryDTO = (await filteredListQuery.ToListAsync())
.Select(x=>new TicketDTO()
{
    Id = x.Id.ToString(),
    Employee = x.Employee.Name,
    Timestamp = x.Timestamp.ToString("g")
}).ToList();

Or if I iterate through filteredListQuery with a foreach loop (which I think is pretty much the same).

Is there an easier (less convoluted and more professional) way to do this (and without the extra unused column on the database)?

CodePudding user response：

Basically, instead of "dynamic", you will use a parameters object, and optionally "if" the parameters are populated, you will add in optional parts of your IQueryable.

public class OrganizationSearchArgs
{
    public string OrganizationNameNotLike { get; set; } = null;

    public ICollection<int> OrgStatuses { get; set; } = new List<int>();

    public ICollection<string> OrganizationNamesIn { get; set; } = new List<string>();

    public ICollection<string> OrganizationNamesNotIn { get; set; } = new List<string>();
}

(below would be a class that exposes functionality on your dbcontext)

    public async Task<int> GetListByArgsCount(OrganizationSearchArgs args, CancellationToken token)
    {
        IQueryable<OrganizationEntity> qry = this.GetGetListByArgsBaseIQueryable(args);
        int returnValue = await qry.CountAsync(token);
        return returnValue;
    }

    public async Task<IEnumerable<OrganizationEntity>> GetListByArgs(
        OrganizationSearchArgs args,
        CancellationToken token)
    {
        IQueryable<OrganizationEntity> qry = this.GetGetListByArgsBaseIQueryable(args);

        List<OrganizationEntity> entities = await qry.AsNoTracking().ToListAsync(token);


        return entities;
    }

    private IQueryable<OrganizationEntity> GetGetListByArgsBaseIQueryable(OrganizationSearchArgs args)
    {
        IQueryable<OrganizationEntity> qry = this.entityDbContext.Organizations
            .Include(org => org.SomeNavigationProperty).AsNoTracking();

        if (!string.IsNullOrEmpty(args.OrganizationNameNotLike))
        {
            qry = qry.Where(o => !o.OrganizationName.Contains(args.OrganizationNameNotLike));
        }


        if (null != args.OrgStatuses && args.OrgStatuses.Any())
        {
            qry = qry.Where(org => args.OrgStatuses.Contains(org.OrgStatus));
        }

        if (null != args.OrganizationNamesIn && args.OrganizationNamesIn.Any())
        {
            qry = qry.Where(org => args.OrganizationNamesIn.Contains(org.OrganizationName));
        }

        if (null != args.OrganizationNamesNotIn && args.OrganizationNamesNotIn.Any())
        {
            qry = qry.Where(org => !args.OrganizationNamesNotIn.Contains(org.OrganizationName));
        }

        return qry;
    }
}

CodePudding user response：

I've found it best to make my actual Entity models reflect the database structure as closely as possible, and largely use them for CRUD operations. You can create a separate class to represent other queryable facets of your entity, but instead of using computed properties, use mapping logic to create a projection for those properties.

public class TicketSummary
{
    public int TicketId {get;set;}
    public Employee LatestEmployee {get;set;}
}

public IQueryable<TicketSummary> BuildSummaryQuery()
{
    return _context.Tickets.Select(t => new TicketSummary
        {
            TicketId = t.Id,
            LatestEmployee = t.History.MaxBy(x=>x.Timestamp).Employee
        });
}

Then you can apply your query criteria as appropriate. Using your dynamic LINQ example above, for example:

var filteredListQuery = BuildSummaryQuery()
    .Where("Employee.Name == \"Batman\"");