I'm trying to execute the following SQL using the generated code. I'm pulling back all the records, then wanting to do a distinct projection filter on the records. Here is the SQL and the code that is currently not working.

SQL:

    SELECT DISTINCT
        ReleaseId,
        BookingNumber,
        Convert(varchar, ShowDateTime, 108) As ShowDateTime
    FROM @TempTable
    Where ReleaseId = @ReleaseId

LLBLGen Collection:

        Public Shared Function GetWeeklyReleaseInfo(ByVal unitNumber As Integer, ByVal startDate As DateTime, ByVal endDate As DateTime, ByVal releaseId As Integer) As DataTable
            Dim boxCollection As New BoxOfficeTransactionHistoryCollection()
            Dim filter As New PredicateExpression()
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.UnitNumber, ComparisonOperator.Equal, unitNumber))
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.ShowDateTime, ComparisonOperator.GreaterEqual, startDate))
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.ShowDateTime, ComparisonOperator.LesserThan, endDate))

            boxCollection.GetMulti(filter)

            filter = New PredicateExpression()
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.ReleaseId, ComparisonOperator.Equal, releaseId))

            Dim boxView As New EntityView(Of BoxOfficeTransactionHistoryEntity)(boxCollection)
            Dim proj As New List(Of IEntityPropertyProjector)()
            proj.Add(New EntityPropertyProjector(BoxOfficeTransactionHistoryFields.ReleaseId, "ReleaseId"))
            proj.Add(New EntityPropertyProjector(BoxOfficeTransactionHistoryFields.BookingNumber, "BookingNumber"))
            proj.Add(New EntityPropertyProjector(BoxOfficeTransactionHistoryFields.ShowDateTime, "ShowDateTime"))

            Dim projection As New DataTable

            boxView.CreateProjection(proj, projection, False, filter)

            Return projection
        End Function

Hi tmaddox. I don't understand what's exactly the part isn't working (1st. fetch, projection don't filter data, projector results don't seem to be distinct)? Also, what version of LLBLGenPro Runtime Libraries are you using?

We are using the newest version, May 23rd.

This projection always returns with a count of 0.

In SQL Server we are using a linked server to populate the first GetMulti (collection) into a temp table, then running the unique query on that.

Both of the collections (whether temp table, or in llblgen) have a row count of 151.

When we run the distinct query, to make only records with unique ReleaseID, ShowDateTime, and BookingNumber in SQL, it gives us 11 records.

When we do the projection in LLBLGen, we always get a row count of 0.

Just wondering if this is the wrong way to do a DISTINCT statement using the LLBLGen Libraries. I could only find one example of howto do this in your documentation, and it was in C# with no VB counterpart. "Generated code - Using the EntityView class, SelfServicing" - "Distinct Projections"

This is basically copied from that.

So basically we have a filled BoxCollection containing the following rows...

BoxCollection (AuditoriumNumber, BOokingNumber, FreePassAttendance, ReleaseId, NetAttendance, BusinessDate, SalesGross, ShowDateTime, UnitNumber).

We would like to run a DISTINCT statement on the filled collection, that then returns as a projection (datatable) with only distinct rows by (BookingNumber, ReleaseId, ShowDateTime).

Or is there a way to do this without using a projection?

Dim boxCollection As New BoxOfficeTransactionHistoryCollection()
            Dim filter As New PredicateExpression()
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.UnitNumber, ComparisonOperator.Equal, unitNumber))
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.ShowDateTime, ComparisonOperator.GreaterEqual, startDate))
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.ShowDateTime, ComparisonOperator.LesserThan, endDate))

            boxCollection.GetMulti(filter)

First of all, make sure the above code returns the correct number of entities. Check boxCollection.Count, if this seems wrong, then please check the generated SQL Query and try to run it directly against the database to check the number of returned rows. Check the manual's section "Using the generated code -> Troubleshooting and debugging", to know how to get the generated SQL query.

            filter = New PredicateExpression()
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.ReleaseId, ComparisonOperator.Equal, releaseId))

            Dim boxView As New EntityView(Of BoxOfficeTransactionHistoryEntity)(boxCollection)

boxView.Filter = filter;

If the previous tests succeeds, then set the filter to the EntityView as shown above, and check the Count property of the entityView to see if the returned count makes sense or not.

Still, if you can't sort it out, then please post your findings on the above tests including the generated SQL query.

What I can't find (but I might have overlooked it in the posts) is: does the collection contain any rows before the projection? And any rows which should meet the filter you specify with the projection?

Distinct with dates/times is always a tough call, as often the time is also stored inside the datatime value, making the value often unique while the distinct should be applied on the data part only.

The initial filter on BoxCollection returns correct.

BoxCollection = 552

Running the releaseId filter on boxView, returns a count of 0. It should return a count of 28.

I switched it to where i'm not running the releaseId filter on the view, which I realized was unneccesary and made it to run on the boxCollection, so...

            Dim boxCollection As New BoxOfficeTransactionHistoryCollection()
            Dim filter As New PredicateExpression()
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.UnitNumber, ComparisonOperator.Equal, unitNumber))
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.ShowDateTime, ComparisonOperator.GreaterEqual, startDate))
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.ShowDateTime, ComparisonOperator.LesserThan, endDate))
            filter.Add(PredicateFactory.CompareValue(BoxOfficeTransactionHistoryFieldIndex.ReleaseId, ComparisonOperator.Equal, releaseId))

            boxCollection.GetMulti(filter)

This sucessfully returns a boxCollection.Count = 28, which is correct. Now I did the following...

            Dim boxView As New EntityView(Of BoxOfficeTransactionHistoryEntity)(boxCollection)

            Dim proj As New List(Of IEntityPropertyProjector)()
            proj.Add(New EntityPropertyProjector(BoxOfficeTransactionHistoryFields.ReleaseId, "ReleaseId"))
            proj.Add(New EntityPropertyProjector(BoxOfficeTransactionHistoryFields.BookingNumber, "BookingNumber"))
            proj.Add(New EntityPropertyProjector(BoxOfficeTransactionHistoryFields.ShowDateTime, "ShowDateTime"))

            Dim projection As New DataTable

            boxView.CreateProjection(proj, projection, False)

            Return projection

EDIT: I managed to fix this and get it to where it returns with a count of 28. I realized the problem as well, as explained below...

Here is the SQL I have been using on the 28 rows to return my 4 distinct rows...

    SELECT DISTINCT
        ReleaseId,
        BookingNumber,
        Convert(varchar, ShowDateTime, 108) As ShowDateTime
    FROM @TempTable

As Otis said, I believe it IS due to the ShowDateTime field. As you can see in the SELECT DISTINCT, I am converting the ShowDateTime to a varchar, the 108 trims the date off, effectively making it just a time in the hh:mm:ss format. So the DISTINCT is ran and looks for unique Times, instead of unique Date/Times. This is why the projection returns 28 rows, because there is 4 unique times for each of the 7 days (thus 2 .

Is there a way I can filter it this way, where I tell the view or the projection to filter the ShowDateTime by Time only, just how i'm doing it in the SQL above?

First of all, Projection has nothing to do with filtering. Projection can be used to project entities in an EntityView to dataTable, or to another entityCollection or to another custom class. Projections are a way to produce custom lists of data ('dynamic lists in memory') based on the current data in the EntityView and a collection of projection objects.

Let's go to the filtering part. Now if you want to filter some data, you either perform the filtering on the database side (upon fetching data), or in memory after the data has been fetched.

Now to for the following Query:

  SELECT DISTINCT
        ReleaseId,
        BookingNumber,
        Convert(varchar, ShowDateTime, 108) As ShowDateTime
    FROM SomeTable

The database is the correct place to execute this query. This can be done using a DynamicList, with an Expression of a DBFunction call (to call the CONVERT function) set to some filed. But calling a CONVERT from a DBFunction call is available in LLBLGen Pro v.2.5 which is in beta now (available for free).

if you don't want to use the new v.2.5, then you can use a storedProcedure to execute the above query adn return the needed resultset.