Entity Framework Confusion with `virtual`s

Question

I am using Entity Framwwork and Code First and getting really confused. I have this class:

public class Blocks
{
    [Display(Name = "ID"),Required(ErrorMessage = "ID is required")]
    [Key,HiddenInput(DisplayValue=false)]
    public int BlockId  { get;set; }

    [Display(Name = "Blocked By"),Required(ErrorMessage = "Blocked By is required")]
    public int ProfileId  { get;set; }

    [Display(Name = "Blocked"),Required(ErrorMessage = "Blocked is required")]
    public int ProfileBlockedId  { get;set; }

    [Display(Name = "Date Blocked"),Required(ErrorMessage = "Date Blocked is required")]
    public DateTime BlockDateTime  { get;set; }

    [Display(Name = "Block Reason")] public string BlockReason  { get;set; }

    public virtual Profiles Profile { get; set; }
    public virtual Profiles ProfileBlocked { get; set; }
}

The profile class is more or less the same and that adds fine and has the correct SQL, but when I run /Blocks I get this error:

MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'Extent1.Profile_ProfileId' in 'field list'
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
  at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior    behavior)

this is because the sql produced is:

SELECT 
  `Extent1`.`BlockId`, 
  `Extent1`.`ProfileId`, 
  `Extent1`.`ProfileBlockedId`, 
  `Extent1`.`BlockDateTime`, 
   `Extent1`.`BlockReason`, 
   `Extent1`.`Profile_ProfileId`, 
  `Extent1`.`ProfileBlocked_ProfileId`
  FROM `Blocks` AS `Extent1`

Notice the Profile_ and ProfileBlocked_. I have them virtual so I have a dropdown of profiles when adding or editing or have the profile name when shown on a list. The strange thing is the other tables. Everything has worked fine except for this one.

Here is the code that creates the wrong SQL and breaks:

//
    // GET: /Blocks/
    public ViewResult Index()
    {
        try {
        return View(context.Blocks.Include(blocks => blocks.Profile).Include(blocks => blocks.ProfileBlocked).ToList());
        }
        catch (Exception ex)
        {
           ModelState.AddModelError("",ex.Message);
           CompileAndSendError(ex);
           return View(context.Blocks.ToList());
        }
    }

I am using:
ASP.net MVC 3
Razor Templates
Entity Framework
MVC Scaffolding [custom T4 ]

Slauma · Accepted Answer · 2011-06-03T23:33:12.947

Give EF a little hint what are your Foreign Key properties by putting annotations on the properties:

...
[ForeignKey("Profile")]
public int ProfileId  { get;set; }
...
[ForeignKey("ProfileBlocked")]
public int ProfileBlockedId  { get;set; }
...

I believe that this is always necessary when you have more than one navigation property referencing to the same target class. The conventions don't detect in this case which properties could be the foreign keys - and EF creates their own FK column names (the Profile_ and ProfileBlocked_ things). And because the column names in the DB are different you get the exception.

(I think, the problem has nothing to do with properties being virtual or not.)

Edit

You can also put the ForeignKey attribute on the navigation properties and specify what's the name of the FK properties:

...
[ForeignKey("ProfileId")]
public virtual Profiles Profile { get; set; }
...
[ForeignKey("ProfileBlockedId")]
public virtual Profiles ProfileBlocked { get; set; }
...

This leads to the same mapping and it's only a matter of taste what you prefer, as far as I can tell.

amazing, excellent that the trick i did have foreign keys on before but never knew what way to point em I.E reference the internal name or reference the ID of the class your bringing in. do you know if it is possible to have the one profile for multiple keys ??? oh and thanks very much — davethecoder, Jun 03 '11 at 22:54
@minus4: What do you mean with "one profile for multiple keys"? But I have the feeling that the answer is No, it's not possible ;) Or create a new question about this... BTW: I've appended an Edit about an alternative to place the FK attribute. — Slauma, Jun 03 '11 at 23:35

score 0 · Answer 2 · edited May 23 '17 at 12:06

I'm facing the same problem, but I can;t sole it using the suggested ForeignKey attribute.

I have installed the MySQL Connector/NET 6.4.3.0. When I run my project I get almost the same error, but referenced to System.Data.Entity. Shouldn;t that be MySql.Data.Entity?

Can you show me how to possibly modify my Web.config or references to work with MySQL.

EDIT

With some help (other post) and trail and error, I got it working too.

Entity Framework Confusion with `virtual`s

2 Answers2