使用Linq对实体进行标准化和抽象化IQueryable构造 [英] Standardizing and Abstracting IQueryable construction with Linq to Entities

问题描述

TL; DR;在大型数据访问层中标准化和抽象IQueryable结构的最佳方法是什么?扩展名是可接受的还是受鼓励的?

背景

我们正在使用带有存储库模式的Entity Framework 6作为我们的数据访问层.

We're using Entity Framework 6 with a Repository pattern as our Data Access Layer.

为了使我们的数据调用更加高效，我们最近开始使用一些结构化的数据传输对象来强迫自己仅从数据库中提取必要的内容.

In order to make our data calls more efficient, we've recently started using some structured Data Transfer Objects to force ourselves to only pull from the database what is necessary.

例如:我们有一个仪表板，该仪表板使用实体映射的数据库表的500个属性中的15个来创建概要文件的分页摘要.

For example: We have a dashboard that uses 15 of 500 properties of an Entity mapped Database Table to create a paged summary of Profiles.

我们不是在转换中提取完整实体并映射，而是从SELECT语句强制转换:

Instead of pulling the full entity and mapping in a convert, we're casting right from a SELECT statement:

//This is a simplified representation
public List<PersonDashboardDTO> GetPeopleByRangeForDashboard(int start, int length)
{
var returnPeople = new List<PersonDashboardDTO>();

IQueryable<PersonForDashboardDTO> People = databaseContext.Profile
    .Where(x => !x.IsDeleted)
    .OrderByDescending(x => x.LastName)
    .Skip(start).Take(length)
    .Select(y => new PersonForDashboardDTO
    {
        Name = String.Concat(y.FirstName, " ", y.LastName),
        Company = y.CompanyContact.Select(x => x.Company.Name).FirstOrDefault(),
        SummaryAddress = y.Address.AddressLine1,
        City = y.Address.City,
        IsEmailOK = y.Notifications.CanSendEmail,
    });

     returnPeople.AddRange(People);
     return returnPeople;
}

虽然这是一个简单的示例，但是其中一些SELECT映射具有150多个属性，这与我的每一个细节都背道而驰，只需简单地一遍又一遍地复制并粘贴即可.

While this is a simple example, some of these SELECT mappings are more than 150 properties, and it goes against every grain of my being to simply copy and paste it over and over again.

似乎也有道理，因为IQueryable直到将其强制转换为另一个对象(like .ToList(), or List.AddRange(IQueryable<>)时才执行，因此我们可以创建方法来以更结构化的方式抽象数据访问调用.

It also seems to stand to reason, that since IQueryable doesn't execute until it is cast to another object (like .ToList(), or List.AddRange(IQueryable<>) that we could create methods to abstract Data Access calls in a more structured manner.

我不确定正确的模式是什么，但这是我在想什么:

I'm not sure what the right pattern is, but here is what I'm thinking:

建议:扩展方法

public static IQueryable<PersonDashboardDTO> MapToPersonDashboardDTO(this IQueryable<Profile> profile)
{
    return profile.Select(y => new PersonDashboardDTO
    {
        Name = String.Concat(y.FirstName, " ", y.LastName),
        Company = y.CompanyContact.Select(x => x.Company.Name).FirstOrDefault(),
        SummaryAddress = y.Address.AddressLine1,
        City = y.Address.City,
        IsEmailOK = y.Notifications.CanSendEmail
    });
}

public static IQueryable<Profile> IsNotDeleted(this IQueryable<Profile> profile)
{
    return profile.Where(x => !x.IsDeleted);
}

public static IQueryable<Profile> OrderedByLastName(this IQueryable<Profile> profile)
{
    return profile.OrderByDescending(x => x.LastName);
}

public static IQueryable<Profile> TakeRange(this IQueryable<Profile> profile, int start, int length)
{
    return profile.Skip(start).Take(length);
}

示例实施

public List<PersonDashboardDTO> GetPeopleByRangeForDashboard(int start, int length)
{
    var returnPeople = new List<PersonDashboardDTO>();

    IQueryable<PersonDashboardDTO> People = databaseContext.Profile
        .IsNotDeleted()
        .OrderedByLastName()
        .TakeRange(start, length)
        .MapToPersonDashboardDTO();

    returnPeople.AddRange(People);

    return returnPeople;
}

摘要

这是一个可以普遍使用的用于标准化我们对EF6进行的查询的可接受的模式吗?这似乎是个不错的选择，但是我在这里的标准和实践中找不到很多，并且会喜欢一些新鲜的眼睛.

Is this an acceptable and usable pattern to use at large to standardize the queries we make with EF6? It seems like a good way to go, but I can't find much in the way of standards and practices here and would love some fresh eyes.

推荐答案

对我来说似乎很合理，但我会进行以下更改:

Seems reasonable to me, but I would make the following changes:

public IQueryable<PersonDashboardDTO> GetPeopleByRangeForDashboard(int start, int length)
{
    return databaseContext.Profile
        .IsNotDeleted()
        .OrderedByLastName()
        .TakeRange(start, length)
        .MapToPersonDashboardDTO();
}

在大多数情况下，没有理由让DAL将结果转换为List而不是仅返回IQueryable，并且如果您的应用程序不需要该对象的某些字段，则可以/应该在枚举之前将其重铸为一个不太完整的对象它.在大多数情况下(这在某些情况下会非常显着)，这将导致更快的数据库访问.尤其是如果不使用地址"之类的字段，则数据库可以删除该联接.

No reason for the DAL to convert the result into a List instead of just returning IQueryable in most cases, and if your application doesn't require certain fields of the object, it could/should recast to a lesser complete object before enumerating it. This will result in faster database access in most cases and in some cases quite dramatically. Especially if a field like Address isn't used, then the database can drop the join.

public static IQueryable<T> TakeRange<T>(this IQueryable<T> profile, int start, int length)
{
    return profile.Skip(start).Take(length);
}

没有理由仅将其附加到返回配置文件的查询中.这将适用于任何IQueryable.

No reason for this to be attached to only a query that returns profiles. This will work on any IQueryable.

这篇关于使用Linq对实体进行标准化和抽象化IQueryable构造的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！