Celtic Coding Solutions Blog

Celtic Coding Solutions Blog - for everything computer related

.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.

Comments are closed