Strange LINQ query error

Question

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.

@abatishchev: Thanks for the tip i didn't know about a null operator like this :) — Rushino, May 30 '11 at 14:56

score 2 · Accepted Answer · edited May 23 '17 at 12:19

2

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.

edited May 23 '17 at 12:19

Community

1
1

answered May 30 '11 at 14:55

Stuart

66,722
7
114
165

Well "aucun" mean None in english. I need to be able to select nothing from the combobox that is why there an union. I will try the concat solution. – Rushino May 30 '11 at 14:58

score 1 · Answer 2 · answered May 30 '11 at 14:46

1

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

answered May 30 '11 at 14:46

abatishchev

98,240
88
296
433

score 0 · Answer 3 · answered May 30 '11 at 14:50

0

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.

answered May 30 '11 at 14:50

Roy Dictus

32,551
8
60
76

I would like to avoid using trial tools. – Rushino May 30 '11 at 15:03

Strange LINQ query error

3 Answers3