An exception occurred with using LINQ method syntax (LINQ to SQL) in C#

Question

I use Linq to Sql and method syntax of Linq in my code to query from database with all tables. And I met an weird exception today, this is the first time error occurred since I started to use L2S.

There are two columns in database table. And the column of "Status" is mapping to program with an enumeration type: 1(Free), 2(Loan).

。Database table schema as below.

。Define table class

private string Type;
private byte Status;

。Query code

string _qNote = string.Empty;
string _qStatus = string.Empty;
List<DefineTableClass> _List = _dbObj.Table.Select(_obj => _obj)
                            .Where(_obj => 
                            (string.IsNullOrWhiteSpace(_qNote) || _obj.Note == _qNote)
                            && (string.IsNullOrWhiteSpace(_qStatus) || Convert.ToInt32(_obj.Status) == Convert.ToInt32(_qStatus))
                            ).ToList();

The exception occurred at

Convert.ToInt32(_obj.Status) == Convert.ToInt32(_qStatus)

and I am wondering this line should be passed at

(string.IsNullOrWhiteSpace(_qStatus)

due to _qStatus is Empty and it should not to do the next check.

The most likely cause is that the `string.IsNullOrWhiteSpace` is not a function that Linq to SQL supports. Try `string == "" || string == null` — Robert Harvey, Aug 16 '21 at 16:29
`Convert.ToInt32(_obj.Status)` may not be supported either. What you could do is try materializing the result of the Linq to SQL query to a list by calling ToList() and then apply your Select to the resulting list; Linq to Objects supports all of the methods you're trying to call. — Robert Harvey, Aug 16 '21 at 16:31
@RobertHarvey I am sure Linq to SQL supports these two functions. Because I have other queries are also using this way to query from database, and it worked. — Vamos, Aug 16 '21 at 18:01
Then edit your question to include the *differences* between the queries that work and the queries that don't work. — Robert Harvey, Aug 16 '21 at 18:06

score 1 · Answer 1 · answered Aug 16 '21 at 16:35

Improve your query by preparing parameters outside Queryable lambda:

string _qNote = string.Empty;
string _qStatus = string.Empty;

var query = _dbObj.Table.AsQueryable();

if (!string.IsNullOrWhiteSpace(_qNote))
    query = query.Where(_obj => _obj.Note == _qNote);

if (!string.IsNullOrWhiteSpace(_qStatus))
{
    var byteStatus = Convert.ToByte(_qStatus);
    query = query.Where(_obj => _obj.Status == byteStatus);
}

var _List = query.ToList();

It should produce more effective SQL and may avoid unwanted conversions.

score 0 · Answer 2 · answered Aug 17 '21 at 03:06

I found the similar situations with my question.

L2S will check ALL where conditions, so it must to do (string.IsNullOrWhiteSpace(_qStatus) no matter the front check we think it's should be passed cause it's true.

The better way to write L2S might less use casting in the syntax and like @SvyatoslavDanyliv said, preparing parameters outside Queryable lambda can prevent the weird logic question as mine at this time.

Reference:

Why isn't this short circuit is not working in linq to sql query?

Why isn't this short circuit in lambda working?

An exception occurred with using LINQ method syntax (LINQ to SQL) in C#

2 Answers2