CODEWITHSUNDEEP
c#sqllinqlinq-to-sqlexception
Rushino
Rushino

Reputation: 9485

Strange LINQ query error

I am trying to databind this query.

 cboTypeStage.DataSource = 
        (
            from ts in tsRepository.GetTable()
            select new { IdTypeStage = Convert.ToDecimal(-1), Description = "Aucun", NomFormEval = "-" }
        )
        .Union
        (
            from ts in tsRepository.GetTable()
            join o in oRepository.GetTable() on ts.IdOrthEvalFormulaire equals o.IdOrthEvalFormulaire
            where (ts.IdProgramme == Convert.ToDecimal(Session["selectedProg"]))
            select new { IdTypeStage = ts.IdTypeStage, Description = ts.Description, NomFormEval = ((o.Nom == null) ? "Aucun" : o.Nom) }
        );

But i can't seem to get it working. i am getting next error:

Unable to cast object of type 'System.Data.Linq.SqlClient.SqlNew' to type 'System.Data.Linq.SqlClient.SqlValue'

I want to convert this SQL query to LINQ

SELECT -1 AS IdTypeStage, 'Aucun' AS Description, '-' AS NomFormEval
UNION
SELECT ts.IdTypeStage AS IdTypeStage, ts.Description AS Description, ISNULL(eref.Nom, 'Aucun') AS NomFormEval FROM TypeStage AS ts
LEFT OUTER JOIN OrthEvalFormulaire AS eref ON eref.IdOrthEvalFormulaire = ts.IdEvalFormulaire 
WHERE IdProgramme = @IdProgramme

Any ideas on the error ?

Thanks.

Upvotes: 2

Views: 514

Answers (3)

Stuart
Stuart

Reputation: 66882

I think you need to use Concat as described in the answers to this question - Inserting extra data in a linq result but not to the data source or waiting for submiting changes on a context object

Additionally, please be aware that 

from ts in tsRepository.GetTable()
        select new { IdTypeStage = Convert.ToDecimal(-1), Description = "Aucun", NomFormEval = "-" }

will give you one extra "aucun" row for each and every row in the repository - I'm guessing that this isn't what you wanted.

Upvotes: 2

Roy Dictus
Roy Dictus

Reputation: 33139

Use a tool such as LINQER to convert the SQL to LINQ. Tools are way better at this than we are :-)

See http://www.sqltolinq.com.

Upvotes: 0

abatishchev
abatishchev

Reputation: 100248

  • Try to use Concat() instead of Join()
  • Try to create not anonymous types but strongly-typed

Upvotes: 1

Related Questions