I have a problem I cant seem to solve. Normally I'd just use a query to perform what I am trying to do, only this time I am using SQL CE.

I basically have 4 Tables.

TblCustomer (PK1: CustomerId)

LnkCustomerLocation (PK1: CustomerId, PK2: LocationId)

TblLicence (PK1: LicenceId, FK1: CustomerId, FK2: LocationId)

TblLicenceCheck (PK1: LicenceCheckId, FK1: LicenceId)

I need to pull back all Customers where a licence has not been checked since a date provided.

I need to filter the Customers and Licences within a certain location.

The steps I am trying to achieve:

1) Get the customers for the LocationId

2) Get the TOP 1 Licence for the LocationId and where related to the TraderIds found in Step 1 (this is done by DateTimeStamp)

3) Get the Top 1 LicenceCheck Record for each related LicenceId in Step 2 (done by DateTimeStamp).

So basically, I want the latest latest licence check for the latest licence for a particular location and trader.

It might appear wrong having both LicenceId link to both Customer and Licence, but trust me there's a lot more going on which means I have to do that. This is just a very simplified version.

where exactly are you stuck?

goose wrote:

where exactly are you stuck?

Basically all of it.

I'm not sure the best way of going about solving it?

I need to be able to get all Customers where there is no licence check for a licence.

Therefor, I need to get the top one "Check" for a "Licence" (by datetimestamp) Get the top 1 "Licence" for a "Customers " (by datetimestamp and locationId)

I also then need to get Customers who's top 1 "Licence" (by dateTimeStamp) does not have an associated "Check" (I think this might have to be a separate query)

To make things easier on both of us and to make it more understandable, would you please post the SQL query that you want to execute so we can try to formulate it with LLBLGen code.

Walaa wrote:

To make things easier on both of us and to make it more understandable, would you please post the SQL query that you want to execute so we can try to formulate it with LLBLGen code.

SELECT   dbo.Tbl_Licence.LicenceId, dbo.Tbl_Customer.CustomerId, dbo.Tbl_Customer.FirstName, dbo.Tbl_Customer.LastName, dbo.Tbl_LicenceCheck.LicenceCheckId,

                      dbo.Tbl_Licence.DateTimeStamp, dbo.Lnk_CustomerTypeLocation.LocationId

FROM         dbo.Tbl_Customer INNER JOIN

                      dbo.Tbl_Licence ON dbo.Tbl_Licence.CustomerId = dbo.Tbl_Customer.CustomerId INNER JOIN

                      dbo.Tbl_LicenceCheck ON dbo.Tbl_LicenceCheck.LicenceId = dbo.Tbl_Licence.LicenceId INNER JOIN

                      dbo.Lnk_CustomerTypeLocation ON dbo.Tbl_Customer.CustomerId = dbo.Lnk_CustomerTypeLocation.CustomerId

WHERE    (dbo.Tbl_LicenceCheck.LicenceCheckId IN

                          (SELECT    TOP (1) LicenceCheckId

                            FROM          dbo.Tbl_LicenceCheck AS Tbl_LicenceCheck_1

                            WHERE     (dbo.Tbl_LicenceCheck.LicenceId = LicenceId)

                            ORDER BY DateTimeStamp DESC)) AND (dbo.Tbl_Licence.LicenceId IN

                          (SELECT    TOP (1) LicenceId

                            FROM          dbo.Tbl_Licence AS Tbl_Licence_1

                            WHERE     (dbo.Tbl_Licence.CustomerId = CustomerId)

                            ORDER BY DateTimeStamp DESC)) AND (dbo.Lnk_CustomerTypeLocation.LocationId = '34a3382c-6b04-45b0-a139-b16d6b6acfde')

Enjoy

SELECT dbo.Tbl_Licence.LicenceId, dbo.Tbl_Customer.CustomerId, dbo.Tbl_Customer.FirstName, dbo.Tbl_Customer.LastName, dbo.Tbl_LicenceCheck.LicenceCheckId, dbo.Tbl_Licence.DateTimeStamp, dbo.Lnk_CustomerTypeLocation.LocationId

You are selecting fields from more than one table/entity, so you need to use a DynamicList.

FROM dbo.Tbl_Customer INNER JOIN dbo.Tbl_Licence ON dbo.Tbl_Licence.CustomerId = dbo.Tbl_Customer.CustomerId INNER JOIN dbo.Tbl_LicenceCheck ON dbo.Tbl_LicenceCheck.LicenceId = dbo.Tbl_Licence.LicenceId INNER JOIN dbo.Lnk_CustomerTypeLocation ON dbo.Tbl_Customer.CustomerId = dbo.Lnk_CustomerTypeLocation.CustomerId

You should use a RelationCollection to pass these relations/Joins to the fetch method.

WHERE (dbo.Tbl_LicenceCheck.LicenceCheckId IN

Use a a FieldCompareSetPredicate to implement the IN subQuery.

                     (SELECT     TOP (1) LicenceCheckId

Pass 1 for the maxNumberOfItemsToReturn of the FieldCompareSetPredicate CTor to specify a Top of 1.

                        FROM         dbo.Tbl_LicenceCheck AS Tbl_LicenceCheck_1
                        WHERE    (dbo.Tbl_LicenceCheck.LicenceId = LicenceId)

Use an alias (entityField.SetObjectAlias("Tbl_LicenceCheck_1"), for the LicenceId of the innerQuery)

eg:

new FieldCompareSetPredicate(LicenceCheckFields.LicenceId, LicenceCheck.LicenceId.SetObjectAlias("LC")  )

Note that the above overload might vary according to the template set you are using (Adapter vs SelfServicing).

ORDER BY DateTimeStamp DESC))

Pass a sort expression.

The rest should be easy now.

[Sorry. I am a bit stuck with the FieldCompareSetPredicate.

I don't know where to go from here:

                ResultsetFields fields = new ResultsetFields(3);

                fields.DefineField(TblLicenceFields.LicenceId, 0, "LicenceId", "Licence");

                fields.DefineField(TblCustomerFields.CustomerId, 1, "CustomerId", "Customer");

                fields.DefineField(TblLicenceCheckFields.LicenceCheckId, 2 "LicenceCheckId", "LicenceCheck");



                IRelationCollection relations = new RelationCollection();

                relations.Add(TblCustomerEntity.Relations.TblLicenceEntityUsingCustomerId, JoinHint.Inner);

                relations.Add(TblLicenceEntity.Relations.TblLicenceCheckEntityUsingLicenceId, JoinHint.Inner);

                relations.Add(LnkCustomerTypeLocationEntity.Relations.TblCustomerEntityUsingCustomerId, JoinHint.Inner);

I am using adapter.

Please check it out in the manual, in the following path: Using the generated code -> Adapter -> Filtering and sorting -> The predicate system

I discovered SubPath's in Prefetch Paths and use that instead. It gave me almost what I wanted, so I finished the rest programmatically.