linq query with a join on a subquery generates invalid sql

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}

I am running a linq query with a join on a subquery and the resulting sql is invalid (see sql below).

I am running against sqlserver2008.  Any idea how to fix this? 

 

EvolveDataModelUnitOfWork uow = Repository.UnitOfWork;

 

var historyGames = (from entity in uow.HistoryGameEntities

                     where entity.GamingTerminalId == terminalId

                     orderby entity.CreatedOn descending

                     select entity).Skip(_maxHistory);

 

var screenShotIds = from screenshot in uow.HistoryScreenshotEntities

                     join history in uow.HistoryEntities on screenshot.HistoryId equals history.Id

                     join historyGame in historyGames on history.HistoryGameId equals historyGame.Id

                     where screenshot.Image != null

                     select screenshot.Id;

 

 

SELECT

  t0.Id AS [t0.Id]

FROM

  HistoryScreenshots t0

INNER JOIN

  Histories t1

ON

  t0.HistoryId = t1.Id

INNER JOIN

  (SELECT

    t0.*

  FROM

    (

      SELECT

        t0.Id AS [t0.Id],

        t0.ApplicationConfigurationId AS [t0.ApplicationConfigurationId],

        t0.CreatedOn AS [t0.CreatedOn],

        t0.GamingTerminalId AS [t0.GamingTerminalId],

        t0.UpdatedOn AS [t0.UpdatedOn],

        ROW_NUMBER() OVER(ORDER BY t0.CreatedOn DESC) as RowNumber

      FROM

        HistoryGames t0

      WHERE

        t0.GamingTerminalId = 1511

    )

    t0

  WHERE

    RowNumber > 15

  ) t2

ON

  t1.HistoryGameId = t2.Id    <---- ERROR this should be t2.[t0.Id]

WHERE

  t0.Image IS NOT NULL

 

 

Ultimately I am trying to set the HistoryScreenshotEntities.Image to null without retrieving the whole entity because the Image data is large and is inefficient to retrieve the entity just to removed the Image field.  So after I retreive the screen shot ids I call update on the unit of work like this:

 

uow.Update(

       new Query(typeof(HistoryScreenshotEntity), Entity.Attribute("Id").In(updateIds)),

       new { Image = (byte[])null });

 

 

Thanks for your help.

Nick

 

This sounds as though it might be related to an issue which was fixed in a nightly build a couple of weeks ago.  If you're not on a recent nightly build, could you upgrade to the current nightly build and give it another go?  Thanks!

I updated to the 11/23 night build and now the generated sql is different for the same linq query( see original post) but still has the same problem (See sql below).  Any other suggestions on how to fix this?

Thank.

Nick

 

SELECT
  t0.Id AS [t0.Id]
FROM
  HistoryScreenshots t0
INNER JOIN
  Histories t1
ON
  t0.HistoryId = t1.Id
INNER JOIN
  (SELECT
    HistoryGames.*
  FROM
    (
      SELECT
        HistoryGames.Id AS [HistoryGames.Id],
        HistoryGames.ApplicationConfigurationId AS [HistoryGames.ApplicationConfigurationId],
        HistoryGames.CreatedOn AS [HistoryGames.CreatedOn],
        HistoryGames.GamingTerminalId AS [HistoryGames.GamingTerminalId],
        HistoryGames.UpdatedOn AS [HistoryGames.UpdatedOn],
        ROW_NUMBER() OVER(ORDER BY HistoryGames.CreatedOn DESC) as RowNumber
      FROM
        HistoryGames
      WHERE
        HistoryGames.GamingTerminalId = 1511
    )
    HistoryGames
  WHERE
    RowNumber > 200
  ) t2
ON
  t1.HistoryGameId = t2.Id  <------- Error.  This should be t2.[HistoryGames.Id]
WHERE
  t0.Image IS NOT NULL

Also, after I upgraded to the nightly build I started getting optimistic concurrency violations.  If I revert to the my previous install (20101027) the optimistic concurrency violations go away.

Did something change in this build around optimistic concurrency?

 

 

Hi Nick,

Would you be able to send through a copy of your model and we can have a look into the querying issue.

Could you elaborate on the optimistic concurrency issues you are seeing - perhaps if you could give a small bit of sample code which we can use to reproduce this with the model and also if you can let us know what the version number of the previous build where you are seeing the expected behavior.

 

Jeremy

Yes, I can send you my model.   What form do you want?  Do you want lightspeed .Ismodel/.cs/.Ismodel.diagram?

Where/how do I send it to you?

 

I didn't look into the concurrency issue much because when I reverted to the previous nightly build (20101027) it worked again.  I will look into it more and see if I can give you more info.

Nick

 

Just the .lsmodel and .lsmodel.diagram please. You can either attach them to the post or if you prefer email them through to us at support at mindscape.co.nz.

 

Jeremy

The model is attached.

Thanks Nick, I have added in a fix for the querying issue which will be available in the next nightly build.

 

Jeremy

Jeremy,

Thanks.  The query works now.  But I can't use that build because I am getting the optimistic concurrency violation with the latest nightly build that I was not getting in nightly build 20101027.

The following code reproduces the concurrency violation.  A fix would be great because I really need to use your previous fix asap.

Thanks,

Nick

        internal static void TestConcurrency()
        {
            try
            {
                var context = new LightSpeedContext<EvolveDataModelUnitOfWork>("localhost");
                var uow = context.CreateUnitOfWork();
                var r = new Random().Next();
                var appClientInstance = new ApplicationClientInstanceEntity
                {
                    ClientId = Guid.NewGuid().ToString(),
                    State = (int)ApplicationClientInstanceState.Started,
                    Session = new SessionEntity
                    {
                        GamingTerminal = new GamingTerminalEntity
                        {
                            Machine = new MachineEntity
                            {
                                AccessKey = new byte[10],
                                AssetTag = "TestMachine-" + r,
                                MacAddress = r,
                                MachineId = r.ToString(),
                            },
                            TerminalModel = new TerminalModelEntity
                            {
                                Name = "TestModel-" + r,
                                TerminalPlatform = new TerminalPlatformEntity { Name = "TestPlatform-" + r }
                            },
                        }
                    }
                };

                uow.Add(appClientInstance);
                long appClientInstanceId = appClientInstance.Id;
                uow.SaveChanges(true);

                var client = (from entity in uow.ApplicationClientInstanceEntities
                              where entity.Id == appClientInstanceId
                              select entity).Single();

                uow.Remove(client);
                uow.SaveChanges();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
        }

Thanks Nick,

I have put in a patch for this into tonights nightly build if you could confirm that fixes the problem you are seeing that would be much appreciated.

 

Jeremy

Jeremy,

 

That patch worked.  I no longer get the currency violation in sql server.

Now the inner join on a subquery doesn't work right for postgres.

The subquery has a sort on it but the sql that is generated does not. 

Btw: I have a INamingStrategy implement for postgres to lower case all the names.

I will attach it to this post.

Thanks again for your help.  I really need to get this working.

Nick

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

var currentScreenshotFilenames = from screenshot in uow.HistoryScreenshotEntities

                                   join history in uow.HistoryEntities on screenshot.HistoryId equals history.Id

                                  join historyGame in

                                         ((from entity in uow.HistoryGameEntities

                                         where entity.GamingTerminalId == terminalId

                                         orderby entity.CreatedOn descending

                                         select entity).Take(_maxHistory)) on history.HistoryGameId equals historyGame.Id

                                  select screenshot.FileName;

 

 

SELECT

  t0.filename AS "t0.filename"

FROM

  historyscreenshots t0

INNER JOIN

  histories t1

ON

  t0.historyid = t1.id

INNER JOIN

  (SELECT

    historygames.id,

    historygames.applicationconfigurationid,

    historygames.createdon,

    historygames.gamingterminalid,

    historygames.updatedon

  FROM

    historygames

  WHERE

    historygames.gamingterminalid = 1488

  LIMIT 10

  ) t2

ON

  t1.historygameid = t2.id

 

Hi Nick,

We dont currently support ORDER BY clauses in sub-selects as there are a number of scenarios across the various providers about when the use of ORDER BY is valid in sub-selects (e.g. I believe Postgres allows is fairly generically, but MySql does not and SqlServer allows is if TOP or FOR XML are specified) so we currently strip these from the subselects.

I can have a look at reviewing this for the case where you have a paging aspect to the query as this would be valid, but again we need to cater for the providers which wont support this as well so it wont be something I can do immediately - Ill add it to our backlog and let you know once we have progressed this.

In the meantime you should look at using a stored procedure for this specific query to work around this limitation.

 

Jeremy