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.

Answer 1

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.

Answer 2

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?