My Software Notes

Useful things I discover

Archive for the ‘Entity Framework’ Category

Entity Framework Generates Weird SQL

leave a comment »

If you ever see Entity Framework code that looks something like this:

SELECT
1 AS [C1],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS varchar(1)) AS [C4]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
WHERE 1 = 0

and your EF LINQ query was nothing like it (and who’s would be?), then you probably have a

somelist.Contains(x.Value)

in your query and the collection “somelist” is empty.

I hit this today and was scratching my head until I found this:  

LINQ to Entity Framework submits meaningless query to SQL Server for Contains(empty array)

Hope that helps someone.

 

Written by gsdwriter

July 17, 2014 at 5:14 pm

MVC 4 Beta – Single Page Application Tutorial – Gotcha

with 6 comments

I’m currently trying out the ASP.NET MVC 4 Beta and I thought I’d give the Single Page Application tutorial a shot.

I’ve created several single page apps so far and they’re working well and people are using them successfully, but now that I’ve had some experience I look at them and wish I could rewrite them from scratch.  If you look back at code you wrote six months ago and don’t think that you could do it better now then either you are perfect or you have stopped learning and are fast becoming antiquated.

Anyway, I want to start using frameworks like knockout.js and backbone.js, and the SPA (Single Page Application) template that comes with MVC 4 uses knockout.js – great.

So I followed the instructions, hit F5 to run the web site, typed “Tasks” in the address bar and … aaargh!  An exceptions in the view and a really unhelpful error message.  “System.InvalidOperationException was unhandled by user code” and “Failed to get the MetadataWorkspace for the DbContext type ‘MvcSpaApp1.Models.MvcSpaApp1Context’.”  The inner exception was no better: “System.Data.ProviderIncompatibleException” and “The provider did not return a ProviderManifestToken string.”

Luckily I’ve been reading up on Entity Framework Code First, so I knew what the problem was – I didn’t have SQL Server Express running.  So I started up SQL Server Express and … still didn’t work.  But this time it was because I didn’t have rights to SQL Server because I was using a machine someone else set up and they had mucked up the security.  Anyway, when I finally got it all sorted out the site ran without any problems.

So just be aware: YOU MUST HAVE SQL SERVER EXPRESS RUNNING and YOU MUST HAVE DATABASE AND TABLE CREATION RIGHTS.

Other than those gotchas the tutorial is good.

Written by gsdwriter

March 2, 2012 at 11:29 am

Entity Framework 4 – The selected stored procedure returns no columns (part 2)

with 5 comments

My original post on this topic, Entity Framework 4 – The selected stored procedure returns no columns, has been getting quite a lot of hits recently and I realize that of all the solutions I gave on that page the one I use the most is the one that’s mentioned way down the page in a comment.  So I thought it would be a good idea to highlight it.

The simplest solution to this issue is based on some data given by Frank Lan, a guy from MS Online Community Support on the SilverLight forums.  He said:

I did some research on how Visual Studio gets the column information. If you open SQL Server profiler when you click “Get Column Information”, you will find that Visual Studio queries against database to get the column information. One query I notice is that Visual Studio tries to execute the store procedure with all parameters set to be null. I am not sure if this is the way Visual Studio determines whether there’s column information or not. But I suggest you trying to execute your query in this way to see if there’s any column returned by your store procedure. Of course you can monitor the profiler your own to find more useful information.

That suggested a simple way to handle any situation where you have applied the solutions I gave in the original article (don’t just ignore those solutions and do this easy one) or where you are forced to use temp tables because temp variables won’t do and you still can’t get the columns:

Stick an “if” statement at the top of your sproc that checks if all parameters are null and if true returns the structure.

For Example:
if @param1 is null and @param2 is null then
begin
select
cast(null as varchar(10)) as Column1,
cast(null as bit) as Column2,
cast(null as decimal) as Column3
end

Just be aware that the casts are required otherwise EF won’t be able to tell what the data types are supposed to be.  Even if you return values instead of nulls you still need the casts.

Hope that helps.

Written by gsdwriter

November 4, 2011 at 2:35 pm

Entity Framework 1.0 (aka 3.5) – Problem in Mapping Fragments

leave a comment »

I have an Entity Framework 1.0 (aka 3.5) project (I wish we could upgrade to 4) and I recently got this error:

Problem in Mapping Fragments starting at lines 1987, 2308: Non-Primary-Key column(s) [ColumnBlah] are being mapped in both fragments to different conceptual side properties – data inconsistency is possible because the corresponding conceptual side properties can be independently modified.

It turns out it was caused because the person who created the table didn’t put a foreign key constraint on the column and so I had to add one.  When I updated the EF model it saw the constraint and added the corresponding navigation property to the entities BUT it kept the original scalar property and was still mapping it to the table column.  So now I had a property in an entity mapped to a column in the underlying table AND a navigation property mapped to the same column.

Simple solution: I deleted the scalar property from the entity.

Hope that helps someone.

 

Written by gsdwriter

November 19, 2010 at 6:15 am

Posted in Entity Framework

Entity Framework 4 – The selected stored procedure returns no columns

with 10 comments

(2011-11-04: I noticed a lot of activity on this post and I realized that the solution I use most is in one of the comments way down the page.  So I have written an additional article here, Entity Framework 4 – The selected stored procedure returns no columns (part 2), to highlight it.  I suggest you do read the solutions given below because they may apply and could help improve your stored procedure but if you are in a hurry the part 2 article gives you a fast fix.)

You create a stored procedure, you bring it into your entity model, you start to create a function import, you click the “Get Column Information” button and you get “The selected stored procedure returns no columns.”

The reasons I’ve seen for this are:

  1. You are returning your result set using dynamic sql, so EF can’t figure out at design time what the shape of the results will be.
  2. You are selecting from a temp table and EF can’t figure out the shape of the results at design time.
  3. Your stored procedure crosses databases and although you have all the rights you need in the database containing the sproc, you don’t have the necessary rights to the database objects in the other database.  E.g., you don’t have select rights on a table in the other database.

The solutions I’ve found are:

For #1

  1. Alter your sproc so the result set structure can be figured out at design time. E.g., put the results into a table variable and select from that.
  2. Create a dummy sproc with the same name and signature as the final sproc that contains a select statement which creates a result set with the desired structure. (E.g., select cast(1 as int), cast(“a” as varchar(20)) … etc.) Refresh your entity model and add the function import – EF will see the structure and you can create the Entity to receive the results. Now replace the dummy sproc in the database with your final version.

For #2

  1. Use a table variable instead of a temp table

For #3

  1. Give the user that is calling the sproc the correct rights in the other database or databases.

I hope people find that useful.

(2010-12-19:  See my reply to minkbear’s comment below for another possible solution.)

Written by gsdwriter

November 2, 2010 at 1:07 pm

Thank you Julie Lerman

with 2 comments

I have a few reasons to thank Julie Lerman.  First her excellent book on Entity Framework (Programming Entity Framework) saved me all sorts of pain and suffering when I recently did a project using EF for data access.  EF is a great tool and will be even better in the future but the first release can be a bit unintuitive at times and especially a pain in the butt when dealing with certain kinds of stored procedures.  Her book saved me from wasting time, wasting resources and the  frustration of going down blind alleys.

Today she saved me again.  I tried to change the namespace of the data access layer of my project and after a couple of minutes of find and replace I could see I was wandering into a minefield.  So I learned first hand that you have to come up with a good namespace right at the beginning because changing it after you’ve got an established EF model makes a trip to the dentist look like fun.

So I right-clicked the folder and selected “Revert”.  And of course that solved everything … not!  Next time I tried to run the app I got this helpful message: “Schema specified is not valid. Errors: Models.XXXX.csdl(3,4) : error 0019: The EntityContainer name must be unique. An EntityContainer with the name ‘XXXX’ is already defined.”

I tried to debug it myself for a while, but no luck so I turned to Bing and found that other people posting to various forums had hit a similar problem with no real solutions provide. Then I found a page on Julie’s site (Entity Framework Error 0019) that solved the whole thing.

So, thanks Julie, and keep writing the great books!

Written by gsdwriter

October 19, 2009 at 11:53 am