Passing empty string to EF6

Question

This code runs fine in the SQL Server Management Studio:

SELECT [PassNumber] FROM [dbo].[Customers] WHERE [PassNumber] <> 'A'

This code also works as expected:

string query = "PassNumber <> 'A'";
DbSqlQuery<Customer> data = db.Customers.SqlQuery( string.Format("select * from customers where {0}", query) );

Now it is changed to exclude empty rows and this code works fine on SSMS:

SELECT [PassNumber] FROM [Customers] WHERE [PassNumber] <> ''

But I can't find a way to correctly format the query string. I have tried all possible combinations that I thought of, but the code throws an exception: "Incorrect syntax near the keyword '<>'."

string query = "PassNumber <> '' ";
DbSqlQuery<Customer> data = db.Customers.SqlQuery( string.Format("select * from customers where {0}", query) );

NOTE: For simplicity, the check for null value has been left out of the examples in the question.

You're probably escaping the apostrophe. Try "PassNumber <> ''''"; (4 apostrophes). — Steve Greene, Feb 04 '16 at 17:24
Can you post the entire sql, it seems like there is an error in your query string based on the error message. — drneel, Feb 04 '16 at 17:36
@drneel That is the entire sql. The error is in how to correctly format the string to send the '' (double apostrophes). — Ben Junior, Feb 04 '16 at 17:40
@drneel The exception is " Incorrect syntax near the keyword '<>'. " — Ben Junior, Feb 04 '16 at 17:48

score 1 · Answer 1 · answered Feb 04 '16 at 17:57

1

Why don't you use LINQ when querying in EF. Then everything might be easier:

var customerList = db.Customers.Where(c => c.PassNumber == string.Empty).ToList();

answered Feb 04 '16 at 17:57

Michael Mairegger

6,833
28
41

I wish I could, but the field name, ("PassNumber" in this case), must also be passed in the query something that I don't know how to do in LINK. – Ben Junior Feb 04 '16 at 18:08
@BenJunior I don't understand... is `PassNumber` not a property of the `Customer` class? – Gert Arnold Feb 04 '16 at 23:06
@Gert Arnold The full query contains two parameters, the field name and the condition. It could be c => c.PassNumber >12 or c => c.State = "FL" . Passing two params is easy in SqlQuery , but not so easy in LINK. – Ben Junior Feb 09 '16 at 21:18
@BenJunior Ah, is that the problem. Maybe you should look at predicate builders, for instance: http://stackoverflow.com/a/14622200/861716 – Gert Arnold Feb 09 '16 at 22:53
@BenJunior why is passing two parameter a problem in LINQ? I don't see any problems with `list.Where(c=> c.PassNumber > 12 || c.State == "FL")` – Michael Mairegger Feb 11 '16 at 08:34

score 0 · Answer 2 · answered Feb 04 '16 at 17:20

0

Have you tried to use

is null

clause on the SQL? Oris not null

answered Feb 04 '16 at 17:20

Angelo Simonato

1
2

the IsNull works fine, but what I need is to check for empty strings – Ben Junior Feb 04 '16 at 17:32

score 0 · Accepted Answer · answered Feb 04 '16 at 23:04

0

If you insist on doing this by raw SQL you should use:

db.Customers.SqlQuery("select * from customers where PassNumber <> @p0, string.Empty);

Or

db.Customers.SqlQuery("select * from customers where LEN(PassNumber) > 0);

answered Feb 04 '16 at 23:04

Gert Arnold

105,341
31
202
291

To me the best answer is the comment you posted about LinkKit. I will accept this one here just to close the thread. – Ben Junior Feb 10 '16 at 02:05

Passing empty string to EF6

3 Answers3