My Software Notes

Useful things I discover

Archive for the ‘Database’ Category

Entity Framework: Contains() (aka ‘Like’) is case insensitive for SQL Server queries

with 3 comments

Just a note to self.

The Contains method on String when used in an Entity Framework “where” clause is case insensitive.

Example:

Assume the “NoteContent” column on the table “Notes” is a varchar column. If I run the following Linq query against the database where db is a DbContext then I get the same result every time.

var num = db.Notes.Where(n => n.NoteContent.Contains(“NoTe”)).Count();

var num = db.Notes.Where(n => n.NoteContent.Contains(“NOTE”)).Count();

var num = db.Notes.Where(n => n.NoteContent.Contains(“note”)).Count();

All produce the same value for “num”.

I’m making this note to self because I keep forgetting and because I keep seeing people do this:

var someString = “whatever”;

var num = db.Notes.Where(n => n.NoteContent.ToUpper().Contains(someString.ToUpper())).Count();

And, to quote Led Zepplin, “It makes me wonder.”

Written by gsdwriter

December 2, 2014 at 9:53 am

Posted in .NET, Database, LINQ

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

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

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)

leave a comment »

Well, that’s a mouthful, isn’t it?

I saw this in our production web site’s log and wondered how serious an issue it is.  Our site is ASP.NET MVC 3, using Entity Framework 4.1 for database connectivity.

I dug around and found the best explanations here:

It’s only appears once in the current log and not at all in the last archived log, so I’m not going to worry about it, yet.  But I thought other people might be interested in what it is, what causes it and what to do about it and I wanted to record the explanation sources in case it happens again and I need to do something about it.

Written by gsdwriter

October 7, 2011 at 10:36 am

Posted in .NET, ASP.NET, Database

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

The Danger of Implicit Data Type Conversions in SQL Server

leave a comment »

I have never before hit the problem that I discovered today because, call me crazy but, I always store dates in a datetime column and not in varchar columns and I rarely use implicit conversions. However, today one of my coworkers was moving some data from an old table that was all varchar columns into a new table that actually defined the column using the data type of the data that was to go into the column (imagine that).

He started getting the date “1900-01-01 00:00:00 000” appearing in the new table and couldn’t figure out why. He asked me to help, so we looked at default constraints- no, triggers – no. Then I had him look at the source data and we saw a lot of empty values in the result of the select query. Then we realized that it wasn’t saying “null” in the result but nothing at all. So we added a “where len(rtrim(ddate)) = 0″ and sure enough the dates were either empty strings or contained spaces and SQL Server was converting them to ‘1900-01-01 00:00:00 000” (the earliest date of the smalldatetime data type – another WTF because the column was a datetime not a smalldatetime).

At first this made no sense to me. The documentation clearly says “When you convert to date and time data types, SQL Server rejects all values it cannot recognize as dates or times.“.  So I decided to play around and I think I figured it out.

SQL Server will implicitly convert a binary value to a datetime.  So if you give it an empty string (and this is my guess) it tries to convert it and fails, so then it tries to use the empty string as a binary and it succeeds, because the empty string has a binary value of zero and that equates to the date “1900-01-01 00:00:00 000”.  

If you give it spaces, SQL Server will trim the spaces and then attempt to do the conversion, so again, it ends up as an empty string.  (Try inserting ‘   1/1/2000  ‘ and you’ll see that SQL Server will convert it correctly despite the spaces, therefore I assume it trims them before attempting the conversion.)

Just try these nonsensical implicit conversions:
create table #tempTest ( Id int null, dtColumn datetime null )
go

insert into #tempTest (Id, dtColumn)
values (1, '')
go

insert into #tempTest (Id, dtColumn)
values (2, 0x0)
go

insert into #tempTest (Id, dtColumn)
values (3, 11)
go

select * from #tempTest
go

select cast(dtColumn as binary) as dtColumnAsBinary from #tempTest
go

Results:

Id          dtColumn
----------- -----------------------
1           1900-01-01 00:00:00.000
2           1900-01-01 00:00:00.000
3           1900-01-12 00:00:00.000

dtColumnAsBinary
--------------------------------------------------------------
0x000000000000000000000000000000000000000000000000000000000000
0x000000000000000000000000000000000000000000000000000000000000
0x000000000000000000000000000000000000000000000000000B00000000

So next time you consider just letting SQL Server implicitly convert your data – think again. You really have no idea what is going to come out the other end.

As a final note, I’ve been using SQL Server since version 1.1 (I guess that gives away my age) and I never hit problems like this before. I think it’s because I rarely use implicit conversions when I’m moving data around and I set a column’s data type to be the type of the data that will be going into it. It’s interesting how these simple practices have insulated me from problems like this for twenty years.

References:

Full list of implicit and explicit conversions supported by SQL Server (scroll down a bit)

All about Data Type Conversion in SQL Server

Written by gsdwriter

March 19, 2010 at 11:31 am

Posted in Database

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