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.

 

Visual Studio Report Designer: Using External Image

Add a table > Link table to a datasource
> Put an image in a cell.
> Image > Properties >
    > “Select the Image Source” to “External”
    > “Use this image” > set to a data source field
> Your Form > Report Viewer > Local Report >Enable External Images > “True”

Example of file names passed:
file:\\ServerName\Directory\SubDirectory\Filename.jpg

see
http://en.wikipedia.org/wiki/File_URI_scheme

but beware of “Percent-encoding” special characters, which include # and others

http://en.wikipedia.org/wiki/Percent-encoding

so ABC#123.jpg will become ABC%23123.jpg

Reference in the manifest does not match the identity of the downloaded assembly

SOLVED

Click-once does not install presenting error message:
“Reference in the manifest does not match the identity of the downloaded assembly …exe”

This could be if the referenced project is an executable, you may find that if you change the reference from a project reference to the executable file then try this may work.
Alternatively and if possible change the referenced project to  Class Library.

UPDATE 17/02/2012
Today I have extracted a Windows Forms from a main project into a smaller executable and referenced it from the main project.
I did a few things including above and then also it did not work until I had published the smaller project and it gained a certificate. Think the error was a validation error.

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

Error on Project Settings “An error occurred while reading the app.config file” when using configSource

Error: on opening Project > Settings
“An error occurred while reading the app.config file. The file might be corrupted or contain invalid XML”
However all functionality works

Occurs when using
<connectionStrings configSource=SharedApp.config></connectionStrings>

Solution: Unknown

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