调用Linq Select()中的方法而不加载整个对象

Question

我使用。net 5和EF Core编写了以下代码:

IQueryable<ExampleViewModel> list = query.Select(x => new ExampleViewModel()
{
Id = x.Id,
BindDate = x.BindDate,
CancellationDate = x.CancellationDate,
ChangeDate = x.ChangeDate,
CreateDate = x.CreateDate,
Description = x.Description,
PrimaryOffice = x.PrimaryOffice == null
? null
: new GenericComboBox()
{
Value = x.PrimaryOffice.Id,
Text =x.PrimaryOffice.LocationName
+ (x.PrimaryOffice.LocationBusinessName == null ? "" : " (" + x.PrimaryOffice.LocationBusinessName + ") ")
+ "("
+ (x.PrimaryOffice.AddressLine1 != null ? x.PrimaryOffice.AddressLine1 + ", " : null)
+ (x.PrimaryOffice.AddressLine2 != null ? x.PrimaryOffice.AddressLine2 + ", " : null)
+ (x.PrimaryOffice.City != null ? x.PrimaryOffice.City + ", " : null)
+ (x.PrimaryOffice.State != null ? x.PrimaryOffice.State.Name != null ? x.PrimaryOffice.State.Name + ", " : null : null)
+ (x.PrimaryOffice.ZipCode ?? null)
+ ")"
}   
});

您看到的PrimaryOffice查询将原样用于其他几个对象。100%相同。所以我想把查询语句移到

后面

PrimaryOffice =

的方法，所以我可以重用它很容易。到目前为止一切顺利。问题是我不能简单地创建一个方法，它以x.PrimaryOffice作为参数，并返回PrimaryOffice类(GenericComboBox)，因为这将加载我的整个'x'对象。

这不是一个选项:

public static GenericComboBox GenericComboBoxCreate(AddressObj x)
{
if (x != null)
{
return new GenericComboBox()
{
Value = x.Id,
Text = x.LocationName
+ ( x.LocationBusinessName == null ? "" : " (" + x.LocationBusinessName + ") " )
+ "("
+ ( x.AddressLine1 != null ? x.AddressLine1 + ", " : null )
+ ( x.AddressLine2 != null ? x.AddressLine2 + ", " : null )
+ ( x.City != null ? x.City + ", " : null )
+ ( x.State != null ? x.State.Name != null ? x.State.Name + ", " : null : null )
+ ( x.ZipCode ?? null )
+ ")"
};
}
return null;
}

然后:

PrimaryAgencyOffice = SomeHelper.GenericComboBoxCreate(x.PrimaryOffice)

我使用Odata，我只需要从DB中获取我真正需要的属性。因为如果我使用上面的方法，EF加载一切，查询远非最优。

我认为Expression<Func< , >>的一些方法应该是解决方案，但我目前还没有想出办法。

如果它是关于整个Select()语句，那就容易了。但它是关于Select中的一个属性。

我猜一些方法与Expression<Func<AgencyAddress, GenericComboBox>>作为参数和返回类型GenericComboBox?

Answer 1

看起来你只需要一个辅助方法来格式化你的GenericComboBox.Text。那么使用静态实用程序方法如何做到这一点呢?

public static string GetAddressString(string locationName, string locationBusinessName, string addressLine1, string addressLine2, string city, string state, string zip)
{
string txt = "{0}{1}({2}{3}{4}{5}{6})";
locationBusinessName = string.IsNullOrEmpty(locationBusinessName) ? string.Empty : string.Format(" ({0})", locationBusinessName);
addressLine1 = string.IsNullOrEmpty(addressLine1) ? string.Empty : string.Format(", {0}", addressLine1);
addressLine2 = string.IsNullOrEmpty(addressLine2) ? string.Empty : string.Format(", {0}", addressLine2);
city = string.IsNullOrEmpty(city) ? string.Empty : string.Format(", {0}", city);
state = string.IsNullOrEmpty(state) ? string.Empty : string.Format(", {0}", state);
zip = string.IsNullOrEmpty(zip) ? string.Empty : string.Format(", {0}", zip);
return string.Format(
txt, 
locationName,
locationBusinessName, 
addressLine1,
addressLine2,
city,
state,
zip
);
}

那么你可以这样调用它:

IQueryable<ExampleViewModel> list = query.Select(x => new ExampleViewModel()
{
.  .  .  .
PrimaryOffice = x.PrimaryOffice == null ? null : new GenericComboBox()
{
Value = x.PrimaryOffice.Id, 
Text = SomeHelper.GetAddressString(
x.PrimaryOffice.LocationName,
x.PrimaryOffice.LocationBusinessName,
x.PrimaryOffice.AddressLine2, 
x.PrimaryOffice.City, 
x.PrimaryOffice.State ? x.PrimaryOffice.State.Name: string.Empty,
x.PrimaryOffice.ZipCode
)
} 
});

你甚至可以把GetAddressString方法放在你的PrimaryOffice类中:

public class PrimaryOffice {
//... your properties....

public string GetAddressString()
{
string txt = "{0}{1}{2}{3}{4}{5}{6}";
string locationBusinessName = string.IsNullOrEmpty(this.LocationBusinessName) ? string.Empty : string.Format(" ({0})", this.LocationBusinessName);
string addressLine1 = string.IsNullOrEmpty(this.AddressLine1) ? string.Empty : string.Format(", {0}", this.AddressLine1);
string addressLine2 = string.IsNullOrEmpty(this.AddressLine2) ? string.Empty : string.Format(", {0}", this.AddressLine2);
string city = string.IsNullOrEmpty(this.City) ? string.Empty : string.Format(", {0}", this.City);
string state = string.IsNullOrEmpty(this.State) ? string.Empty : string.Format(", {0}", this.State.Name);
string zip = string.IsNullOrEmpty(this.Zip) ? string.Empty : string.Format(", {0}", this.Zip);
return string.Format(
txt, 
this.LocationName,
locationBusinessName, 
addressLine1,
addressLine2,
city,
state,
zip
);
}
}

然后像这样访问

PrimaryOffice = x.PrimaryOffice == null ? null : new GenericComboBox()
{
Value = x.PrimaryOffice.Id, 
Text = x.PrimaryOffice.GetAddressString()
}

或你可以在PrimaryOffice类中为PrimaryOfficeName添加一个自定义Getter:

Answer 2

解决方案之一是使用LINQKit来完成这样的任务。

在构建选项时启用扩展:

builder
.UseSqlServer(connectionString)
.WithExpressionExpanding(); // enabling LINQKit extension

按以下方式实现你的方法:

[Expandable(nameof(GenericComboBoxCreateImpl))]
public static GenericComboBox GenericComboBoxCreate(this AddressObj x)
=> throw new NotImplementedException();
private static Expression<Func<AddressObj, GenericComboBox>> GenericComboBoxCreateImpl()
{
return x => x == null ? null :
new GenericComboBox()
{
Value = x.Id,
Text = x.LocationName
+ ( x.LocationBusinessName == null ? "" : " (" + x.LocationBusinessName + ") " )
+ "("
+ ( x.AddressLine1 != null ? x.AddressLine1 + ", " : null )
+ ( x.AddressLine2 != null ? x.AddressLine2 + ", " : null )
+ ( x.City != null ? x.City + ", " : null )
+ ( x.State != null ? x.State.Name != null ? x.State.Name + ", " : null : null )
+ ( x.ZipCode ?? null )
+ ")"
};
}

和使用的好处:

IQueryable<ExampleViewModel> list = query.Select(x => new ExampleViewModel()
{
Id = x.Id,
BindDate = x.BindDate,
CancellationDate = x.CancellationDate,
ChangeDate = x.ChangeDate,
CreateDate = x.CreateDate,
Description = x.Description,
PrimaryOffice = x.PrimaryOffice.GenericComboBoxCreate()
});