none
Probleman with Union RRS feed

  • Question

  • Hi

    My Linq above don't work. The error message is showed: "SqlException: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."


    What am I doing wrong?

    thanks.

    Code Snippet

    var cc = (from pro in PRODUTOs
    join obr in PRODUTOOBRIGATORIOs on pro.CODIGOPRODUTO equals obr.CODIGOPRODUTO
    join dis in DISTRITOs on obr.CODIGODISTRITO equals dis.CODIGODISTRITO
    join are in AREAATUACAOs on dis.CODIGODISTRITO equals are.CODIGODISTRITO
    where are.CODIGOAREAATUACAO == 84
    select new {CODIGOPRODUTO = pro.CODIGOPRODUTO,
    VISAOOBRIGATORIA =
    new bool?(true),
                VISIVEL =
    new bool?(false)} )
    .Union
    (
    from pro in PRODUTOs
    join ces in CESTAPRODUTOS on pro.CODIGOPRODUTO equals ces.CODIGOPRODUTO
    where ces.CODIGOAREAATUACAO == 84
    select new {CODIGOPRODUTO = pro.CODIGOPRODUTO,
    VISAOOBRIGATORIA =
    new bool?(false),
                VISIVEL =
    new bool?(false)} );

    var cc2 = (
    from pro in PRODUTOs
    join sap in PRODUTOSAPs on pro.CODIGOPRODUTO equals sap.CODIGOPRODUTO
    join prod in cc on pro.CODIGOPRODUTO equals prod.CODIGOPRODUTO into temp
    from t in temp.DefaultIfEmpty()
    where sap.CODIGOATIVO == 1
    select new {sap.CODIGOSAP,
                pro.CODIGOPRODUTO,
                pro.NOMEPRODUTO,
                VISAOOBRIGATORIA = t ==
    null ? false : t.VISAOOBRIGATORIA});


    Marcos Antonio Aguiar Jr
    Monday, February 2, 2009 12:53 PM

Answers

  • Hi Damien.

    Thanks for help me.

    I did that. I used to let and decided

    var cc = (from pro in PRODUTOs
    join obr in PRODUTOOBRIGATORIOs on pro.CODIGOPRODUTO equals obr.CODIGOPRODUTO
    join dis in DISTRITOs on obr.CODIGODISTRITO equals dis.CODIGODISTRITO
    join are in AREAATUACAOs on dis.CODIGODISTRITO equals are.CODIGODISTRITO
    let VISAO = true
    where are.CODIGOAREAATUACAO == 84
    select new {
                 CODIGOPRODUTO = pro.CODIGOPRODUTO,
                 VISAOOBRIGATORIA =
    new bool?(true),
                 VISIVEL = VISAO}).Union
    (
    from pro in PRODUTOs
    join ces in CESTAPRODUTOS on pro.CODIGOPRODUTO equals ces.CODIGOPRODUTO
    let VISAO = true
    where ces.CODIGOAREAATUACAO == 84
    select new {CODIGOPRODUTO = pro.CODIGOPRODUTO,
                VISAOOBRIGATORIA =
    new bool?(false),
                VISIVEL = VISAO
    });

    Is this is incorrect? the result was the least expected.

    Thanks.

    Marcos Antonio Aguiar Jr
    Tuesday, February 3, 2009 10:44 AM

All replies

  • There is unfortunately a bug in LINQ to SQL whereby the duplicate-column optimization process fails to detect it is running within the context of a union.

    This is scheduled to be fixed in .NET 4.0. In the mean time for your example above I would suggest changing the second query to perform some server-based logic instead of one of the "false" statements to prevent the column from being duplicated, e.g. VISIVEL = new bool?(ces.CODIGOAREAATUACAO <> 84).

    [)amien
    Tuesday, February 3, 2009 8:28 AM
    Moderator
  • Hi Damien.

    Thanks for help me.

    I did that. I used to let and decided

    var cc = (from pro in PRODUTOs
    join obr in PRODUTOOBRIGATORIOs on pro.CODIGOPRODUTO equals obr.CODIGOPRODUTO
    join dis in DISTRITOs on obr.CODIGODISTRITO equals dis.CODIGODISTRITO
    join are in AREAATUACAOs on dis.CODIGODISTRITO equals are.CODIGODISTRITO
    let VISAO = true
    where are.CODIGOAREAATUACAO == 84
    select new {
                 CODIGOPRODUTO = pro.CODIGOPRODUTO,
                 VISAOOBRIGATORIA =
    new bool?(true),
                 VISIVEL = VISAO}).Union
    (
    from pro in PRODUTOs
    join ces in CESTAPRODUTOS on pro.CODIGOPRODUTO equals ces.CODIGOPRODUTO
    let VISAO = true
    where ces.CODIGOAREAATUACAO == 84
    select new {CODIGOPRODUTO = pro.CODIGOPRODUTO,
                VISAOOBRIGATORIA =
    new bool?(false),
                VISIVEL = VISAO
    });

    Is this is incorrect? the result was the least expected.

    Thanks.

    Marcos Antonio Aguiar Jr
    Tuesday, February 3, 2009 10:44 AM
  • Unfortunately according to the following post on Microsoft Connect it appears this issue won't be addressed for .NET Framework 4.0. I seriously hope this is not true.

    https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=355734
    Thursday, July 23, 2009 12:23 PM
  • Hi All,

    Im struck in same problem. Anyone tried any workaround?

    Thanks

    Amal k


    BY Amal

    Saturday, December 22, 2012 12:18 AM