Celtic Coding Solutions Blog

Celtic Coding Solutions Blog - for everything computer related

Entity Framework determine storage version SQL Server 2016

Using latest Entity Framework 6.1.3 with Visual Studio 2015 and connecting to SQL Server 2016 developer edition, I had the following error:-

Could not determine storage version; a valid storage connection or a version hint is required.

Fix:-

In Visual Studio Solution Explorer.

Right Click over the Entity Framework .edmx

Open with and select XML Text Editor

Search for "ProviderManifestToken=" and either you will have XXXX or in my case I had 2012 in this value.  So I changed it to 2008 and now my code works.

Entity Framework SQL Server Version Error

Used Entity Framework 6 in Visual Studio 2015 and pulled all the table from the Development Database I needed.  They changed the Entity Framework SQL Configuration line in the App.Config (.Net Command Line App) and got the following error when trying to insert a record:-
The version of sql server in use does not support datatype 'datetime2'. entity framework

Check my SQL Database tables and I wasn't using DateTime2 in any fields.  OK this is a bit strange, the only difference is that Dev SQL Server is running SQL 2008 R2 and Live is still stuck on SQL 2005.

Fix:-
Right click on the *.edmx (Entity Framework Model) file and select "Open With..."
Select "XML (Text) Editor"
Near the top of the XML there is a section that looks like :-
<Schema Provider="System.Data.SqlClient" ProviderManifestToken="2008" Alias="Self" 
The catch here is the ProviderManifestToken is still on 2008, so change this to 2005 for SQL Server 2005 and the error should go:-
<Schema Provider="System.Data.SqlClient" ProviderManifestToken="2005" Alias="Self" 

.Net Entity framework not adding SQL View

Whilst trying to add a SQL View to an Entity Framework Model I notice it wasn't being added.  Normally this is down to a table not having a Primary Key, but how do you add a Primary Key to a SQL View?  Well there is a simplier method to solving this issue and it is as follows:-

Say we had a simple Select:-

SELECT 'Data1F1' as Field1, 'Data1F2' as Field2

UNION ALL

SELECT 'Data2F1' as Field1, 'Data2F2' as Field2

Now this doesn't have an identity field or ID Primary Key so Entity Framework would reject this.  So lets add a ROW_NUMBER value:-

SELECT

ROW_NUMBER() OVER (ORDER BY Field1) as ID, *

FROM

(

SELECT 'Data1F1' as Field1, 'Data1F2' as Field2

UNION ALL

SELECT 'Data2F1' as Field1, 'Data2F2' as Field2

) as DataValues

 

However again Entity Framework doesn't like this because the ROW_NUMBER is actually returning a Nullable field.  So now we need to how much for abortion convert this into a NOT NULL Value.  To do this we just change the first SELECT:-

From

ROW_NUMBER() OVER (ORDER BY Field1) as ID, *

To

ISNULL(ROW_NUMBER() OVER (ORDER BY Field1),0) as ID, *

 

The ISNULL makes sure pregnancy pills there are no nulls because we are returning a Zero if there are and SQL interprets this as a NOT NULL field.

Now we can finally add this SQL View to our Entity Framework model.