CODEWITHSUNDEEP
sqlentity-frameworklinq-to-sqldata-annotations
Jay
Jay

Reputation: 3082

Datatype mismatch mapping existing database to EF

I am using an existing production database to create MVC application but due to a mismatch of datatypes I am unable to correctly set virtual properties of the parent class. My code is as follows:

public class bill {

public int billId { get; set; }

.........

}


public class meter {

public int meterId { get; set; }

public int billId { get; set; }

.....

}


public class payment {

public int paymentId { get; set; }

public long billId { get; set; }

......
}

I added the following to the bill class:

public virtual ICollection<meter> Meters { get; set; }
public virtual ICollection<payment> Payments { get; set; }

I am able to retrieve related meters but due to the datatype long of billid in payment I get an error. Is there any way of mapping this correctly without my changes to code first model affecting the original database?

I am getting the error:

Invalid column name 'bill_billId'.
Invalid column name 'bill_billId'.
Invalid column name 'bill_billId'.

When attempting to display as follows: 

 @foreach (var m in Model.Meters.Where(x => x.archived != true && x.meterUnitsUsed > 0))
 {
      <tr>
           <td>@Html.DisplayFor(x => m.meterSerialNumber)</td> 
           <td>@Html.DisplayFor(x => m.meterOpeningRead)</td>
           <td>@Html.DisplayFor(x => m.meterClosingRead)</td>
           <td>@Html.DisplayFor(x => m.meterUnitsUsed)</td>
           <td>@Html.DisplayFor(x => m.archived)</td>
           <td>@Html.DisplayFor(x => m.meterUnitType)</td>
           <td>@Html.DisplayFor(x => m.meterUnitPrice)</td>
           <td>@Html.DisplayFor(x => m.meterAmount)</td>

           @foreach (var i in Model.LiftsRefunds.Where(i => i.archived != true))
           {
                <td>@Html.DisplayFor(x => i.liftRefundDate)</td>
                <td>@Html.DisplayFor(x => i.liftRefundAmount)</td>
                <td>@Html.DisplayFor(x => i.liftType)</td>
           }
      </tr>
 }

The database currently in use is set up incorrectly to use long datatype for the child class while the primary key of the parent class is int. Are there any means of setting EF to ignore this and map the long to int so that the payments could be referenced as virtual properties?

Upvotes: 2

Views: 1069

Answers (2)

bubi
bubi

Reputation: 6501

I think you have 2 different issues.

===
First of all, payment.billId is a reference to bill.billId that we suppose they have 2 different columns (one per table) on SQL Server.
1. If you ignore migrations (actually you are ignoring migrations and is ok) your program will work also if the data types in SQL Server are different from the data types of the classes i.e. int and bigint on SQL Server and int and int on your classes.
2. BUT SQL Server can't make a relationship from bigint column to int column so in your DB you can't have bigint and int for the two columns OR you don't have a relationship (you don't have to, EF will work anyway but it's not a good practice).

So I suggest you check what you are doing on SQL Server then change data type of the classes (i.e. set all int) and disable migrations.

===
Now, why the error about non existing field?
In your classes you have a mapping configuration issue. You have to connect payment.billId property with public virtual ICollection<payment> Payments { get; set; }. I use fluent mapping and one mapping file per class so in this case in the mapping file of payment I write 

HasRequired(t => t.bill)
    .WithMany(t => t.Payments)
    .Map(d => d.MapKey("billId"));

You can do the same with attributes.

Upvotes: 1

Maciej Los
Maciej Los

Reputation: 8591

For error:

The 'billId' property on 'payment' could not be set to a 'System.Int64' value. You must set this property to a non-null value of type 'System.Int32'

follow the instructions here:

In your EDMX, if you go under your Y table and click on X column, right-click, click on Properties, scroll down to Nullable and change from False to True.

If you get a "mapping fragment" error, you'll have to delete the table from the EDMX and re-add it, because in the Model Browser it stores the table properties and the only way to refresh that (that I know of) is to delete the table from the Model Browser under .Store then retrieving it using Update Model from Database.. command.

For further information, please see MSDN documentation: How to: Update an .edmx File when the Database Changes (Entity Data Model Tools)

Upvotes: 0

Related Questions