Error 3031: Problem in mapping fragments starting at line… nullable column

Error 3031: Problem in mapping fragments starting at line… nullable column

In SQL I added a column to a table and initially had the column as Not Nullable. I then updated a view to use the new column. I then updated Entity Framework model from the database. I then changed my mind and made the column nullable. I then updated EF model once again and it would not compile. No-manner of updating column Nullable properties worked.

After some reading suggested this was a bug in EF. As a precaution I updated the view in SQL to refresh the schema went to EF and removed and added it again.

Advertisements

Entity Framework possible feedback to Microsoft Connect – IBindingListView

 

https://connect.microsoft.com/data/feedback/details/704105/entity-framework-winforms-datagridview-support-for-bindingsource-filter-sort-and-column-heading-sort

 

Entity Framework WinForms DataGridView support for BindingSource.Filter .Sort and column heading Sort

Entity Framework looks useful and I have limited use of it in my code. Also several developers have expressed to me how they have found it useful.

I currently use a lot of WinForms databinding using DataSets and BindingSource. Out of the box I get column header sorting. Also it is a simple matter to set BindingSource.Filter and BindingSource.Sort as strings. I have used this feature to provide a lot of data-driven filter and sort strings for UI quick filter and sort which works very well.

However when I use Entity Framework the column header sort and BiningSource.Filter and .Sort properties do not work. My understanding is that this is because EF does not support IBindingListView. Although it might but not out of the box. Please advise or consider implementing this in future editions of Entity Framework.

Thank you

Stephen, UK

Entity Framework: Stored Procedure – Function import

How to…get a stored procedure from SQL and use it in code
In this case execute only, not entity CRUD

  1. Model Browser 
  2. Stored Procedure
  3. Right-click
  4. Update Model from database
  5. Get your stored procedure
  6. However at this point still unusable in code
  7. Not obvious step > Double click Stored Procedure
  8. Choose function import options
  9. OK creates function import which is then useable in code
  10. Note all parameters are surpisingly nullable – May be there is a way preventing this. Unsolved

 

Enjoy !

Entity Framework: WinForms databinding in EF4.1 – DBSet

Solved

Dim context As New SOPEntities
System.Data.Entity.DbExtensions.Load(context.Employees.Where(Function(f) f.Current = True).OrderBy(Function(g) g.FullName))
EmployeeBindingSource.DataSource = context.Employees.Local
ListBox1.DataSource = System.Data.Entity.DbExtensions.Tobindinglist(context.Employees.Local)
ListBox1.DisplayMember = “FullName”

Problem

‘local’ is not a member of ‘System.Data.Objects.ObjectSet(Of <entityname>)’

See http://wp.me/p17IS4-ih

 

Solved

Open the <Name>Model.edmx > Right click > Add code generation item…

Choose ADO.Net DbContext Generator. This has affect of changing the .edmx file Code Generation Strategy to “None” and creates a text template model and .Local now works.

Discussion

Uses EF4.1 EntityFramework.dll
Database first, then add code generation item to get T4 simpler classes and DBSet inherits DBContext
Initially could not work out how to get the Load method. See above for solution.
There is no MSDN VB.Net sample yet. The C# is more like context.Employees.Local.ToBindingList. So it took me a while to work out the syntax above.

Unsolved:
Also the FullName is now a computed column in the database. Was unable to work out how to do this in EF. It may come in the future as a “Model Defined Function”. Do not think this is a Scalar or Complex Type which is something else.
Unsolved:
For BindingSource to support complex filter it needs to support IBindingListView. Typically EF does not.

Links

Binding Objects to Controls
http://msdn.microsoft.com/en-us/library/gg197521(v=VS.103).aspx

Following is in C#
http://msdn.microsoft.com/en-us/library/gg197523(v=VS.103).aspx

Entity Framework: Generating Strongly Typed Entity Classes: Lightweight

Lightweight

Step by step here: http://msdn.microsoft.com/en-us/data/gg685494

Videos here:  http://msdn.microsoft.com/en-us/data/aa937723

Entity Framework “Update Model from Database” generates unexpected self-reference associations / navigation properties

EF4.1 Entity Framework “Update Model from Database” generates unexpected self-reference associations / navigation properties

Check in SQL server on the table design, if a relationship started to be formed, but was not completed then it may be left on the table. Removing this unnecessary relationship and then using “Update Model from Database” will remove these extra associations

Entity Framework: Error 111: Properties referred by the Principal Role…

The following error message may contain entity name or other project specific text

Error 111: Properties referred by the Principal Role must be exactly identical to the key of the EntityType referred to by the Principal Role in the relationship constraint for Relationship . Make sure all the key properties are specified in the Principal Role.

Solution upgarde to EF4.1 or higher.

 

Entity Connection String at run-time

 

Following is useful

Public Shared Function GetRunTimeEntityConnectionString(EntityConnectionStringName As String) As String
Dim currentConString = Configuration.ConfigurationManager.ConnectionStrings(EntityConnectionStringName).ConnectionString
Dim builder As New EntityClient.EntityConnectionStringBuilder(currentConString)
builder.ProviderConnectionString = <“NewConString”>
‘builder.Metadata = “res://*/LookupModel.csdl|res://*/LookupModel.ssdl|res://*/LookupModel.msl”
‘builder.Provider = “System.Data.SqlClient”
Return builder.ConnectionString
End Function

Usage
Dim EntityConnectionString = RuntimeConnectionString.GetRunTimeEntityConnectionString(<EntityConStringName>)
context = New <Namespace>.EntityModel.<EntityName>Entities(EntityConnectionString)

If trying to change actual SharedApp.config as follows then currently get an error.
Configuration.ConfigurationManager.ConnectionStrings(<“Name of Entity ConString”>).ConnectionString = RunTimeEntityConnectionString
Error:
system.configuration.configurationerrorsexception the configuration is read only

There are suggested methods for solving this online

LINQ and Entity Framework binding to ComboBox and DataGridViewComboBox

Private context As BC.SWT.EntityModel.SOPEntities

Private Sub LoadLookupData()

Try

    Dim EntityConnectionString = Configuration.ConfigurationManager.ConnectionStrings(“SOPEntities”).ConnectionString
    context = New BC.SWT.EntityModel.SOPEntities(EntityConnectionString)

    Dim CustomerQuery As ObjectQuery(Of Customer) = context.Customers
    ‘CustomerIDComboBox.DataSource = CustomerQuery.Execute(MergeOption.AppendOnly) ‘works but includes all columns
    Dim results = From a In CustomerQuery Select a.CustomerID, a.Customer1 Order By Customer1
    CustomerIDComboBox.DataSource = results
    CustomerIDComboBox.DisplayMember = “Customer1”

    CustomerIDDataGridViewComboBoxColumn.DataSource = results.ToList
    CustomerIDDataGridViewComboBoxColumn.DisplayMember = “Customer1”
    CustomerIDDataGridViewComboBoxColumn.ValueMember = “CustomerID”
    CustomerIDDataGridViewComboBoxColumn.DataPropertyName = “CustomerID”

Catch ex As Exception
    MessageBox.Show(ex.Message, Me.Text)
End Try

End Sub

LINQ samples

To use LINQ need

Imports System.Linq
May need reference to System.Core and System.Data
‘LINQ
Dim MRU As IEnumerable(Of PadMain) = From pm In mruList OrderBy pm.PadMaterial

LINQ to Entity Framework

Private
context As BC.SWT.EntityModel.SOPEntities

Dim EntityConnectionString = Configuration.ConfigurationManager.ConnectionStrings(“SOPEntities”).ConnectionString
context = New BC.SWT.EntityModel.SOPEntities(EntityConnectionString)

Dim
CustomerQuery AsObjectQuery(OfCustomer) = context.Customers
Dim results = From a In CustomerQuery Select a.CustomerID, a.Customer1 OrderBy Customer1