PredicateBuilder,使用EF构建超过2个表的谓词 [英] PredicateBuilder, build predicate over 2 tables using EF

查看:492
本文介绍了PredicateBuilder,使用EF构建超过2个表的谓词的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个复杂的问题,但它的解释更复杂(我想)。不过,如果不清楚,请稍等一下,请问我详细说明。



我有一个名为 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, Rooms 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆