1

I have a fairly simple Web API 2.2 (EF6) setup where I have Users and Preferences. Users can have many Preferences, and Preferences can have many Users, so I have a join table called UserPreferences. Here are those tables:

CREATE TABLE [dbo].[Users](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [Email] [varchar](50) NULL)

CREATE TABLE [dbo].[Preferences](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [PossibleValues] [nvarchar](250) NULL)

CREATE TABLE [dbo].[UserPreferences](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [PreferenceId] [int] NOT NULL,
    [UserId] [int] NOT NULL,
    [Value] [nvarchar](50) NULL)

As you can see, a Preference has a list of possible values (comma-delimited), and a UserPreference has a Value, which should be what the User has selected as their chosen value. So, for example, if the PossibleValues of the Preference are 'Blue,Green,Red,White', the Value of the UserPreference might be 'Blue'. From the way I understand it, this type of relationship is many-to-many, but since I have added another field to the join table (Value), Entity Framework can't handle this as a many-to-many. According to this question's answer, I have to create two one-to-many relationships for this to work. So I have followed the advice of that post:

public partial class User
{
    public User()
    {
        Preferences = new HashSet<UserPreference>();
    }
    public int Id { get; set; }
    [Required]
    public string FirstName { get; set; }
    [Required]
    public string LastName { get; set; }
    [Required]
    public string Email { get; set; }

    public virtual ICollection<UserPreference> Preferences { get; set; }
}

public partial class Preference
{
    public Preference()
    {
        Users = new HashSet<UserPreference>();
    }
    public int Id { get; set; }
    [Required]
    public string Name { get; set; }
    [Required]
    public string PossibleValues { get; set; }
    public virtual ICollection<UserPreference> Users { get; set; }
}

public partial class UserPreference
{
    public int Id { get; set; }

    [Key,Column(Order = 0)]
    public int UserId { get; set; }
    [Key, Column(Order = 1)]
    public int PreferenceId { get; set; }
    public virtual User User { get; set; }
    public virtual Preference Preference { get; set; }
    public string Value { get; set; }
}

I think that looks right, but when I query the service using a Controller method like this in my UsersController:

// GET: odata/Users(5)/Preferences
[EnableQuery]
public IQueryable<UserPreference> GetPreferences([FromODataUri] int key)
{
    return _db.Users.Where(m => m.Id == key).SelectMany(m => m.Preferences);
}

I get an exception stating "Invalid object name: 'dbo.UserPreferences'."

  "message": "Invalid object name 'dbo.UserPreferences'.",
  "type": "System.Data.SqlClient.SqlException",

I clearly have the table UserPreferences, so I can only assume it doesn't look like EF expects it to, but I have no idea what I am supposed to do to get this to work. I don't need to get a list of Users for a Preference, but I do need to get a list of Preferences for a User, and I just cannot get that to work.

Community
  • 1
  • 1
Eddie
  • 1,228
  • 3
  • 17
  • 31
  • Has the table been created in the database? Are you using Code First Migrations? – jjj Jul 22 '15 at 21:51
  • Try to map class `UserPreference` explicitly to table `UserPreferences`, maybe the pluralizing algorithm has a glitch. – Gert Arnold Jul 22 '15 at 22:03
  • No, I'm not using code first migrations. I've already created the tables. The only thing I'm generating using EF are the Web API controllers. – Eddie Jul 22 '15 at 22:03
  • @GertArnold - Something like this? modelBuilder.Entity() .Map(m => m.ToTable("UserPreferences")); I tried that, but still get the same exception. – Eddie Jul 22 '15 at 22:38
  • This is all your Code First code?, let solve this problem. – Jose Rodriguez Jul 22 '15 at 22:40
  • That's what I aimed at, yes. This is weird... It's a `SqlException`, which means that EF generates the query alright but that the database doesn't have the table. I can't but conclude that you're connecting to a wrong database. – Gert Arnold Jul 22 '15 at 22:55
  • Sometimes the simplest solutions... Yes, I have two versions of the db, and I completely forgot about that. I was connecting to the wrong db. Thank you for your help. – Eddie Jul 22 '15 at 23:01

1 Answers1

0

Delete

public int Id { get; set; }

from UserPreference class.

My code:

public class AgreementCourse
{
    [Key, Column(Order = 0)]
    [ForeignKey("Agreement")]
    public int AgreementId { get; set; }
    public virtual Agreement Agreement { get; set; }

    [Key, Column(Order = 1)]
    [ForeignKey("Course")]
    public int CourseId { get; set; }
    public virtual Course Course { get; set; }

    public CourseTypeEnum CourseType { get; set; }
}
szydzik
  • 981
  • 8
  • 9