Query-problem

Hi,

I have sent you an example via mail...

I have created a query, which looks like this:

        IQueryable<WG> query =
          uow.WG_VPs.Where(c => c.Begindate <= Till && c.Enddate >= From).Select(
            c => c.WG).Distinct();
        query = query.Where(wg => !wg.IsFlag);
        query.ToList();

This query will not execute on my mssql-server because of duplicate column-names. I tried to build an example for the problem, but till now I was not able to. Every time I get a correct query:

SELECT
  t0.wg_id AS [t0.wg_id],
  t0.a AS [t0.a],
  t0.b AS [t0.b],
  t0.isflag AS [t0.isflag],
  t0.WGHId AS [t0.WGHId]
FROM
  (
    SELECT
      t0.wg_id,
      t0.a,
      t0.b,
      t0.isflag,
      t0.WGHId
    FROM
      wg t0
    INNER JOIN
      wg_vp t1
    ON
      t0.wg_id = t1.WGId
    WHERE
      (t1.begindate <= '20.06.2011 00:00:00' AND t1.enddate >= '20.06.2011 00:00:00')
  )
  t0
WHERE
  NOT (t0.isflag = True)

The querysources seem to be quite identical and the debugger gives me identical statements but the sql is different. The inner select does not only contain the columns for t0 but also the ones for t1

[...]

FROM
  (
    SELECT
      t0.wg_id,
      t0.a,
      t0.b,
      t0.isflag,
      t0.WGHId

      t1.Id,
      t1.a,
      t1.b,

[...]

As both t1 and t2 habe same column names this will not work.

I do not understand when this problem occurrs. The odd thing is: When I remove the row marked bold, everything works fine.

Regards,

Dennis

Hi Dennis,

Can you check if you sent through the email as we have not recieved anything through. If the attachment contains any binaries or script files could you either use a RAR file instead or remove them as we use GMail and that strips any attachments it doesnt like (although it happily lets any RAR file through).

I have tried to quickly create a model which resembles what you have above but I am not seeing the extra set of columns in the query unfortunately so I will wait for you to send through the example.

Thanks!

Jeremy

Hi Dennis,

I have recieved the example through now and can run this up, but this produces the following SQL for me which seems to be working fine. Are you getting the duplicate column names when you run it there?

---

SELECT

  t0.wg_id AS [t0.wg_id],

  t0.a AS [t0.a],

  t0.b AS [t0.b],

  t0.isflag AS [t0.isflag],

  t0.WGHId AS [t0.WGHId]

FROM

  (

    SELECT

      t0.wg_id,

      t0.a,

      t0.b,

      t0.isflag,

      t0.WGHId

    FROM

      wg t0

    INNER JOIN

      wg_vp t1

    ON

      t0.wg_id = t1.WGId

    WHERE

      (t1.begindate <= '2011-06-21 00:00:00' AND t1.enddate >= '2011-06-21 00:00:00')

  )

  t0

WHERE

  NOT (t0.isflag = 1)

Jeremy

Hi,

this example is as far as I got. I did not get the duplicate columns there and I do not have the time to investigate further. I hoped that these hints would be enough for you to reproduce the problem.

Regards,

Dennis

Unfortunately without being able to replicate the problem here we cant look much further into this. If you are able to provide any more information or a subsequent repro case then please send those through and we can continue to try and investigate this.

Jeremy