PredicateBuilder,使用EF构建超过2个表的谓词 [英] PredicateBuilder, build predicate over 2 tables using EF
问题描述
我有一个复杂的问题,但它的解释更复杂(我想)。不过,如果不清楚,请稍等一下,请问我详细说明。
我有一个名为 UserService
。服务是用户提供的任何服务,例如在咖啡厅播放的乐队。另一个是DJ和房间。这是表格的样子:
[Table(UserService)]
public class UserService
{
public int Id {get;组; }
public string UserId {get;组; }
public string Name {get;组; }
public string信息{get;组; }
public Type Type {get;组; }
[ForeignKey(UserId)]
public virtual ApplicationUser User {get;组;
}
类型
一个枚举
为所有可能的服务。
这个房间是一个特殊情况,因为它需要一些额外的字段不是总是需要/需要,如地址和地理位置。由于这些原因,我为此创建了一个单独的表:
public class Room
{
public int Id {get;组; }
public int UserServiceId {get;组; }
public string Street {get;组; }
public string ZipCode {get;组; }
public int HouseNumber {get;组; }
public string City {get;组; }
public DbGeography GeoLocation {get;组; }
[ForeignKey(UserServiceId)]
public virtual UserService UserService {get;组; }
}
当过滤用户服务(所有组合都可能)时,我构建一个 PredicateBuilder 。假设用户选择乐队和房间,但是当选择房间时,用户还应该传递城市或邮政编码(或任何其他地址以获得地理定位),并且距离以英里寻找一个房间。如何建立超过2个表的谓词?
如果我这样做:
var userServices = PredicateBuilder.Create< UserService>(x => lstTypes.Contains(x.Type));
比起我不能对 GeoLocation
字段在Room表中,因为 UserService
没有它。所以这不行:
userServices = userServices.And(x => x.GeoLocation.Distance(geo)< ;距离);
另外,由于 Orderby(),我需要这些用户服务组合。 ().Take()
我使用。因此,如果我把它们分开并将数据库转到两次,那么这样就会搞砸了。
我正在考虑添加导航属性
到 UserService
,但我不知道这在短期和长期可能做什么。因为并不总是需要。
现在你有事情,房间
s与 UserService
有着多对一的关系,因为它们具有该表的外键。
您可以按原样离开,并将客房
属性添加到 UserService
,在这种情况下,您可以必须看看是否 x => x.Rooms.Any(r => r.GeoLocation.Distance(geo)< distance)
。如果没有与服务相关联的空间,则 .Any()
将显然返回false。
然而,您的声音就像一个房间只是一个可选属性,从 UserService
,在这种情况下,您可能想要使 UserServiceId
其主键及其外键。那么你的谓词将是 x => x.Room.GeoLocation.Distance(geo)<距离
。奇怪的是,虽然C#会从此代码中抛出异常,但生成的SQL最终会传播空值,所以它应该有效地出现 false
如果 x.Room
证明是空的。
I've got a complex problem, but the explanation of it is even more complex (I think). But I'll give it a try anyway, if it's not clear, please ask me to elaborate.
I have a table called UserService
. A service is whatever a user offers, for example a band that plays in a cafe. Another one is DJ and room. Here is what the table looks like:
[Table("UserService")]
public class UserService
{
public int Id { get; set; }
public string UserId { get; set; }
public string Name { get; set; }
public string Information { get; set; }
public Type Type { get; set; }
[ForeignKey("UserId")]
public virtual ApplicationUser User { get; set; }
}
Type
is an enum
for all possible services.
The room is a special case because it requires some additional fields that are not always required/needed, like address and geolocation. For these reasons I created a separate table for it:
public class Room
{
public int Id { get; set; }
public int UserServiceId { get; set; }
public string Street { get; set; }
public string ZipCode { get; set; }
public int HouseNumber { get; set; }
public string City { get; set; }
public DbGeography GeoLocation { get; set; }
[ForeignKey("UserServiceId")]
public virtual UserService UserService { get; set; }
}
When filtering on a user service (all combo's are possible), which I construct with a PredicateBuilder.
Suppose the user selects Band and Room but when room is selected, the user should also pass a city or zip code (or anything else to get a geolocation from) and a distance in miles to look for a room. How can I build the predicate over 2 tables?
If I do this:
var userServices = PredicateBuilder.Create<UserService>(x => lstTypes.Contains(x.Type));
Than I can't do anything with the GeoLocation
field in the Room table, because UserService
doesn't have it. So this won't work:
userServices = userServices.And(x => x.GeoLocation.Distance(geo) < distance);
Also, I need these userservices combined because of the Orderby().Skip().Take()
I use. So this will get messed up if I separate them and going twice to the database because of this.
I was thinking about adding a navigation property Room
to UserService
but I'm not sure what this might do in the short and long term. Since it's not always required.
The way you have things now, Room
s effectively have a many-to-one relationship with UserService
because they have a foreign key to that table.
You could leave this as-is, and add a Rooms
property to UserService
, in which case you'd have to see whether x => x.Rooms.Any(r => r.GeoLocation.Distance(geo) < distance)
. If there is no room associated with the service, then the .Any()
would obviously return false.
However, you're making it sound like a Room is simply an optional property off of UserService
, in which case you may want to make UserServiceId
its primary key as well as its foreign key. Then your predicate would be x => x.Room.GeoLocation.Distance(geo) < distance
. The weird thing is that while C# would throw an exception from this code, the generated SQL ends up propagating null values so it should come out effectively false
if x.Room
turns out to be null.
这篇关于PredicateBuilder,使用EF构建超过2个表的谓词的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!