3

I have a code first db using EF 6.
I'm facing the similar issue. I tried all the answers but none of them worked out.

My entities are:

public class UtilizationSummary
{
    public DateTime SummaryDate { get; set; }
    public Int32 ProcessKey { get; set; }
    public virtual ProcessInfo ProcessInfo { get; set; }
    public Guid MachineId { get; set; }
    public virtual Machine Machine { get; set; }
}

public class ProcessInfo
{
     public virtual Machine Machine { get; set; }
     public virtual Session Session { get; set; }
}

public class Machine
{
     public Guid Id { get; set; }
     public String Sid { get; set; }
}

Mappings are as follows:

public class UtilizationSummaryMap: EntityTypeConfiguration<UtilizationSummary>
{
     this.HasKey(pus=> new { pus.MachineId, pus.SummaryDate, pus.ProcessKey });
     this.Property(pus=> pus.MachineId).IsRequired();
     this.HasRequired(pus=> pus.Machine).WithMany().HasForeignKey(ma => ma.MachineId);
}

Accessing using:

[HttpGet]
[ODataRoute("UtilizationSummary")]
[EnableQuery]
public IQueryable<UtilizationSummary> FilterUtilizationSummary() 
{
    var expression = ...some expression..;
    var result = db.UtilizationSummary.Where(expression);
    return result;
}

The following exception is being thrown at 'return result':

"message":"Invalid column name 'Machine_Id'.","type":"System.Data.SqlClient.SqlException"

Printing result gives:

SELECT 
    [Extent1].[ProcessKey] AS [ProcessKey], 
    [Extent1].[MachineId] AS [MachineId], 
    [Extent1].[SummaryDate] AS [SummaryDate],  
    [Extent1].[Machine_Id] AS [Machine_Id]
FROM [ProcessUtilizationMinuteSummary] AS [Extent1]

I'm not getting why it is trying to access Machine_Id though i have given the mapping for foriegn key.

Thanks in advance :)

Boghyon Hoffmann
  • 17,103
  • 12
  • 72
  • 170
Nameless
  • 1,026
  • 15
  • 28
  • Are you sure the table `[Extent1]` having column `Machine_Id` – sujith karivelil Mar 31 '17 at 06:49
  • [Extent1] is actually 'ProcessUtilizationMinuteSummary'. It doesn't have Machine_Id. – Nameless Mar 31 '17 at 06:53
  • @un-lucky [Extent1] not having column `Machine_Id` is the problem here – Sentry Mar 31 '17 at 06:53
  • But i have MachineId which is the foriegn key to Machine as declared in mapping. – Nameless Mar 31 '17 at 06:54
  • Could it be that EF6 has problems with the primary key being a `Guid` and not a `long`? – Sentry Mar 31 '17 at 06:56
  • @Sentry seems i have a table in which Guid being the type of primary key and is working fine. – Nameless Mar 31 '17 at 07:05
  • i even added `.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)` on MachineId. Still the same issue. – Nameless Mar 31 '17 at 07:08
  • It would seem there is confusion as to whether its "MachineID" or "Machine_ID" .. these would be different... – BugFinder Mar 31 '17 at 07:09
  • @BugFinder But still, i wonder why it references `Machine_Id` when i have given `MachineId` as the foriegn key? – Nameless Mar 31 '17 at 07:11
  • 1
    First, `EntityTypeConfiguration` should be `EntityTypeConfiguration` (hope it's a typo). Second, remove the `HasDatabaseGeneratedOption` line - FKs cannot be generated by database. Third and more important, looks like the issue is caused by `public virtual ProcessInfo ProcessInfo { get; set; }`. What's the purpose of that property? Is this the real model? What is `ProcessInfo` class - complex type, entity type or ?? – Ivan Stoev Mar 31 '17 at 08:58
  • @IvanStoev Sorry my bad! Thanks for pointing out. Corrected the typos. PrcessInfo is an entity type. It has many primitive members but i chose not to include them. I'm sure they are not causing problem. – Nameless Mar 31 '17 at 09:53
  • @IvanStoev do i have to somehow show that Machine has relation with `UtilizationSummary`? using - .WithMany() ? – Nameless Mar 31 '17 at 09:56
  • 1
    Ok, then do you have something like `public ICollection ...` in `Machine` class? Because if you do have, and don't specify it in `WithMany` EF will assume second `one-to-many` relationship with hidden `Machine_Id` property. – Ivan Stoev Mar 31 '17 at 09:56
  • @IvanStoev Nope. I made sure i don't use ICollection which creates columns on its own. – Nameless Mar 31 '17 at 09:58
  • Hey! Specifying `.WithMany(ma => ma.UtilizationSummary)` on `Machine` worked! Seems it couldn't resolve 1-many relation. – Nameless Mar 31 '17 at 10:01
  • 1
    Check https://msdn.microsoft.com/en-us/library/jj591620(v=vs.113).aspx. One-to-one unidirectional relationship: ` .HasRequired(t => t.Machine) .WithRequiredPrincipal();` Try it. – Mark Shevchenko Mar 31 '17 at 10:03
  • 1
    Cool. So at the end you **do have** such collection. To avoid future issues, `With` methods should always use the correct overload depending on presence/absence of a navigation property. Happy coding. – Ivan Stoev Mar 31 '17 at 10:04
  • @IvanStoev I don't see any collection :O But i'm glad it worked. Can u post the answer please :) – Nameless Mar 31 '17 at 10:34
  • Then what is `ma.UtilizationSummary`? Anyway, glad it helped, but more likely there are similar questions here, so let just move on :)- – Ivan Stoev Mar 31 '17 at 10:37
  • `ma=>ma.UtilizationSummary` tells that `Machine` has relation with UtilizationSummary rit? – Nameless Mar 31 '17 at 10:41
  • @MarkShevchenko Thanks! but `WithRequiredPrincipal()` makes it `required:required` relation and i have to have foriegn key to UtilizationSummary from Machines, which isn't what i need. – Nameless Mar 31 '17 at 10:49

0 Answers0