PredicateBuilder, build predicate over 2 tables using EF

Question

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.

score 0 · Accepted Answer · answered Mar 05 '14 at 23:27

0

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.

answered Mar 05 '14 at 23:27

StriplingWarrior

151,543
27
246
315

It's a one-to-one relationship, what makes you think many-to-one? Do you mean `public Room Room {get; set;}`? Or with the `virtual` key word? And yes, the room is 'optional' because the user decides what kind of service he/she offers. If its 'dj' than no point of giving a room up since you're invited to a location in stead of having it at the house of the user. – Quoter Mar 05 '14 at 23:33
@Quoter: What happens if you go to your database and insert two Rooms with the same `UserServiceId`? – StriplingWarrior Mar 05 '14 at 23:40
Nothing, it will just get committed. Why? It's 1 pm here, I can't think straight anymore, my bad. – Quoter Mar 05 '14 at 23:42
@Quoter: The answer *should be* "The database won't let me because the constraints won't allow it," but I'm guessing that is not the case here, because the standard way to set up a one-to-one relationship is how I described it--a primary key that is also the foreign key. Other developers, looking at your database tables, would say that you have a many-to-many relationship. – StriplingWarrior Mar 05 '14 at 23:43
But yes, you've got the general idea: once you've got your schema set up to prevent adding more than one room per UserService, you add a `Room` navigation property in the usual way. But you recognize that the Room property may be null for some UserService objects. – StriplingWarrior Mar 05 '14 at 23:45
Could you show me how the models would look like exactly and maybe if something needs to be done with `FluentApi`? I keep getting an error when Update-Database command is executed that starts with `An error occurred while saving entities that do not expose foreign key properties for their relationships...` – Quoter Mar 06 '14 at 00:19
@Quoter: I'm afraid I'm not experienced with code-first, but I imagine [something like this](http://stackoverflow.com/questions/14701378/implementing-zero-or-one-to-zero-or-one-relationship-in-ef-code-first-by-fluent) will give you what you need. – StriplingWarrior Mar 06 '14 at 16:36
1

I implemented that like a half a day ago and it seems to work after extensively testing. Thanks. – Quoter Mar 06 '14 at 16:46

PredicateBuilder, build predicate over 2 tables using EF

1 Answers1

Linked