Conversion failed when converting the nvarchar value 'xxx' to data type int.

I have the following query:

Collection is a nvarchar, SerialNumber is an int and reference is a string.

It generates the following SQL:

  SELECT

    ...

  FROM

    [Folder]

  WHERE

    (([Folder].[Collection] + '-') + [Folder].[SerialNumber]) = 'xxx'

Which when executed raises the following error:

System.Data.SqlClient.SqlException : Conversion failed when converting the nvarchar value 'xxx' to data type int.

Minus the fantastic MS styles cruft...

I have the following query:

UnitOfWorkScope.Current.Folders.Where(f => f.Collection + "-" + f.SerialNumber == reference.Trim()).SingleOrDefault();

Collection is a nvarchar, SerialNumber is an int and reference is a string.

It generates the following SQL:

  SELECT
    ...
  FROM
    [Folder]
  WHERE
    (([Folder].[Collection] + '-') + [Folder].[SerialNumber]) = 'xxx'

Which when executed raises the following error:

System.Data.SqlClient.SqlException : Conversion failed when converting the nvarchar value 'xxx' to data type int.

Unfortunately the core LightSpeed SQL engine doesn't support queries that involve conversion, and requires some infrastructure changes to enable this.

I've logged this as an issue, but in the meantime suggest flipping the query around to something like:

string collectionName = reference.Substring(0, reference.LastIndexOf('-')).Trim();
int serialNumber = Int32.Parse(reference.Substring(reference.LastIndexOf('-') + 1));

var query = UnitOfWorkScope.Current.Folders.Where(
  f => f.Collection == collectionName && f.SerialNumber == serialNumber);