Hello,

            var sites = (from c in TransactionManager.Meta.VRESite
                             select c).WithPath(opath => opath.Prefetch<VRESiteStatusEntity>(p => p.VRESiteStatusUsingVRESiteId)
                                 .OrderByDescending(x => x.StatusDate).LimitTo(1).SubPath(spath => spath.Prefetch<StatusEntity>(k => k.StatusUsingStatusId)));

                return sites.ToList();

The above doesn't work as expected. I do get only one status but it's not the correct one (based on the order by that's in there). It's just returning the first item in the rowset.

I've tried swapping around the LimitTo and Order method calls to see if that made a difference. It didn't. Theres no TOP or ORDER BY stuff appearing in the SQL so i'm assuming it's (supposed to be) done in memory.

LLBL 2.6 - 15th may build runtimes Adapter pattern

Would you please post the 3 generated Queries?

1- Query for: VRESiteEntity 2- Query for: VRESiteStatusEntity 3- Query for: StatusEntity

Sure:

1

SELECT [LPLA_1].[VRESiteId], [LPLA_1].[Name], [LPLA_1].[Domain], [LPLA_1].[CurrentPrice], [LPLA_1].[CurrentOwnershipId], [LPLA_1].[IsActive], [LPLA_1].[UpdatedBy], [LPLA_1].[UpdatedOn], [LPLA_1].[CreatedBy], [LPLA_1].[CreatedOn] FROM [VRESites].[dbo].[VRESite] [LPLA_1] 

2

exec sp_executesql N'SELECT [VRESites].[dbo].[VRESiteStatus].[VRESiteStatusId], [VRESites].[dbo].[VRESiteStatus].[VRESiteId], [VRESites].[dbo].[VRESiteStatus].[StatusId], [VRESites].[dbo].[VRESiteStatus].[StatusDate], [VRESites].[dbo].[VRESiteStatus].[StatusChangedById] FROM [VRESites].[dbo].[VRESiteStatus]  WHERE ( [VRESites].[dbo].[VRESiteStatus].[VRESiteId] IN (@VRESiteId1, @VRESiteId2, @VRESiteId3))',N'@VRESiteId1 int,@VRESiteId2 int,@VRESiteId3 int',@VRESiteId1=4,@VRESiteId2=6,@VRESiteId3=3

3

exec sp_executesql N'SELECT [VRESites].[dbo].[Status].[StatusId], [VRESites].[dbo].[Status].[Name] FROM [VRESites].[dbo].[Status]  WHERE ( [VRESites].[dbo].[Status].[StatusId] IN (@StatusId1, @StatusId2, @StatusId3))',N'@StatusId1 int,@StatusId2 int,@StatusId3 int',@StatusId1=7,@StatusId2=6,@StatusId3=5

Please post the DDL of the involved tables, as this could be a field issue. Also be sure you are using the latest builds.

DDL means what? You want the schema? There'll be a few irrelevant references.

/****** Object:  Table [dbo].[VRESite]  Script Date: 05/29/2009 15:43:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VRESite](
    [VRESiteId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [Domain] [nvarchar](128) NOT NULL,
    [CurrentPrice] [money] NOT NULL,
    [CurrentOwnershipId] [int] NULL,
    [IsActive] [dbo].[Flag] NOT NULL,
    [UpdatedBy] [nvarchar](64) NOT NULL,
    [UpdatedOn] [smalldatetime] NOT NULL,
    [CreatedBy] [nvarchar](64) NOT NULL,
    [CreatedOn] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_VRESite] PRIMARY KEY CLUSTERED 
(
    [VRESiteId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[VRESite]  WITH CHECK ADD  CONSTRAINT [FK_VRESite_SiteOwnership] FOREIGN KEY([CurrentOwnershipId])
REFERENCES [dbo].[SiteOwnership] ([SiteOwnershipId])
GO
ALTER TABLE [dbo].[VRESite] CHECK CONSTRAINT [FK_VRESite_SiteOwnership]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VRESiteStatus](
    [VRESiteStatusId] [int] IDENTITY(1,1) NOT NULL,
    [VRESiteId] [int] NOT NULL,
    [StatusId] [int] NOT NULL,
    [StatusDate] [smalldatetime] NOT NULL,
    [StatusChangedById] [int] NOT NULL,
 CONSTRAINT [PK_VRESiteStatus] PRIMARY KEY CLUSTERED 
(
    [VRESiteStatusId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[VRESiteStatus]  WITH CHECK ADD  CONSTRAINT [FK_VRESiteStatus_Status] FOREIGN KEY([StatusId])
REFERENCES [dbo].[Status] ([StatusId])
GO
ALTER TABLE [dbo].[VRESiteStatus] CHECK CONSTRAINT [FK_VRESiteStatus_Status]
GO
ALTER TABLE [dbo].[VRESiteStatus]  WITH CHECK ADD  CONSTRAINT [FK_VRESiteStatus_User] FOREIGN KEY([StatusChangedById])
REFERENCES [dbo].[User] ([UserId])
GO
ALTER TABLE [dbo].[VRESiteStatus] CHECK CONSTRAINT [FK_VRESiteStatus_User]
GO
ALTER TABLE [dbo].[VRESiteStatus]  WITH CHECK ADD  CONSTRAINT [FK_VRESiteStatus_VRESite] FOREIGN KEY([VRESiteId])
REFERENCES [dbo].[VRESite] ([VRESiteId])
GO
ALTER TABLE [dbo].[VRESiteStatus] CHECK CONSTRAINT [FK_VRESiteStatus_VRESite]

/****** Object:  Table [dbo].[Status]   Script Date: 05/29/2009 15:44:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Status](
    [StatusId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED 
(
    [StatusId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Prefetch paths need data. So it's a bit cumbersome to reproduce it with just the schema. A little background info: the Limit stuff is not done inside the query: if you have 10 customers and you want to fetch their last 10 orders, you can't use TOP, you have to wade through all the orders and count how much are fetched per parent (as you can't apply TOP relatively on a subset).

The ordering should be there though.

I can't reproduce it on northwind:

[Test]
public void GetAllCustomersWithTheirLast2OrdersJez()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.ParameterisedPrefetchPathThreshold = 1;
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = (from c in metaData.Customer
                 where !(new string[] { "FISSA", "PARIS" }.Contains(c.CustomerId))
                 select c).WithPath(customerPath => customerPath
                        .Prefetch<OrderEntity>(c => c.Orders).OrderByDescending(o => o.OrderDate).LimitTo(2));

        int count = 0;
        foreach(var v in q)
        {
            count++;
            Assert.IsTrue((v.Orders.Count > 0) && (v.Orders.Count <= 2));
        }
        Assert.AreEqual(89, count);
    }
}

The ordering is there, also with a different theshold.

If you're wondering about the ordering on the SUBpath filter, then that's indeed not there, due to a performance issue it can give. However it should never be a problem because it is accompanied with a TOP, which thus means the filter is parameter based, and ordering isn't necessary.

Yes I coudn't see how the the top and order could be done in SQL either.

This is an internal project so I don't see a problem with a just sending you the db. (attached)

So if you get your list of VRESites using that query and fixate on the one with ID 3 I expect there to be only a single VRESiteStatus in it's VRESiteStatusUsingVRESiteId collection. That VRESiteStatus should be the one with VRESiteStatusId-6 as it has the most recent StatusDate (29/05/2009) for that VRESite.

If you're wondering about the ordering on the SUBpath filter, then that's indeed not there, due to a performance issue it can give. However it should never be a problem because it is accompanied with a TOP, which thus means the filter is parameter based, and ordering isn't necessary.

In my query the ordering is on the prefetch not the subpath.

I think you are mistaken about your data

the folowing query:

SELECT *
  FROM VRESiteStatus
  Order By StatusDate Desc

Produces the following output:

VRESiteStatusId VRESiteId StatusId StatusDate StatusChangedById 4 3 5 2009-10-11 00:00:00 3 6 3 7 2009-05-29 10:12:00 4 5 6 6 2009-05-28 18:20:00 3 3 3 7 2009-05-28 11:56:00 4 2 3 7 2009-05-28 11:24:00 4 1 3 6 2009-05-27 12:02:00 4

This shows that the most recent StatusDate is 2009 -10- 11 which corresponds to VRESiteStatusId = 4.

Thus the code is working perfectly.