Some quirks with Projection

Hi,

I ran into some issues while trying to do projection. Your API documentation states this should work:

IList pricingInfo = unitOfWork.Project(query, typeof(PricingInfo));

 So I tried: IList RecentInventoryItems = uow.Project(qry, typeof(InventoryItemCheckoutHistory));
but this does not work since IList requires a type argument.

So I change it to:
IList<InventoryItemCheckoutHistory> RecentInventoryItems = uow.Project <InventoryItemCheckoutHistory>(qry);

and

var RecentInventoryItems = uow.Project(qry,typeof(PricingInfo));

They both seem to work okay is there any real difference? Are they the same in performance?

I am then databinding  RecentInventoryItems to a repeater and using something like this access it's related entities:
<asp:Label ID="I_lb" runat="server" Text='<%# ((InventoryItemCheckoutHistory)Container.DataItem).InventoryItemSample.InventoryItem.Item %>' /> 

This fails and I get an 'Object reference not set to an instance of an object.'

So I used this as a hack:
IList<InventoryItemCheckoutHistory> list = new List<InventoryItemCheckoutHistory>(); 

foreach (InventoryItemCheckoutHistory i in RecentInventoryItems)
{
uow.Add(i);
list.Add(i);
}

And then binding my repeater to 'list'  instead of 'RecentInventoryItems'. Then it works okay.

I understand that Projection only returns the field specified and not the entire entity, but my query and IList<> is strongly typed and it is already attached
to the unit of work. So why won't it worked?

Is there a more straight forward way of doing this? Or should I be using named aggregates/eager loading?

Also, is the API documentation out of date? This is not the first time I've found a discrepancy.

Thanks,

SR.

Your first fragment:

IList RecentInventoryItems = uow.Project(qry, typeof(InventoryItemCheckoutHistory));

does work, you just need to bring System.Collections into scope, because it returns an IList (non-generic, weak typed) rather than an IList<T>.

Your second and third fragments are equivalent. Project<T>(query) just calls Project(query, typeof(T)) and casts the result from an IList to an IList<T> for you. Performance will be identical apart from the cast (which you would probably want to do anyway!)

Your fourth fragment fails because projections do not associate the returned object with a unit of work. (Normally when you're projecting it's because you want a non-entity type -- if you wanted an entity type you'd just use Find.) Because there is no UOW, LightSpeed can't traverse the association so the association returns null.

This explains why your fifth fragment works: because you are manually associating the returned objects with a unit of work, which gives them a database connection on which to perform association lookups.

The more straightforward way is to use Find instead of Project.

[quote user="SR8"]is the API documentation out of date?[/quote]

A lot of our documentation needs work, but in this case I think the documentatation is correct. Note in particular the Remarks on the Project method docs: "The results of the projection are not entities, and are not associated with the unit of work."

Hmm, seems our lovely forum software decided to trash the formatting there.  Reposting for
readability (I hope!)...

Your first fragment:

IList RecentInventoryItems = uow.Project(qry, typeof(InventoryItemCheckoutHistory));

does work, you just need to bring System.Collections into scope, because it returns an
IList (non-generic, weak typed) rather than an IList<T>.

Your second and third fragments are equivalent. Project<T>(query) just calls Project(query, typeof(T))
and casts the result from an IList to an IList<T> for you. Performance will be identical apart from the
cast (which you would probably want to do anyway!)

Your fourth fragment fails because projections do not associate the returned object with a unit of work.
(Normally when you're projecting it's because you want a non-entity type -- if you wanted an entity type
you'd just use Find.) Because there is no UOW, LightSpeed can't traverse the association so
the association returns null.

This explains why your fifth fragment works: because you are manually associating the returned
objects with a unit of work, which gives them a database connection on which to
perform association lookups.

The more straightforward way is to use Find instead of Project.

[quote user="SR8"] is the API documentation out of date?[/quote]

A lot of our documentation needs work, but in this case I think the documentation is correct. Note in
particular the Remarks on the Project method docs: "The results of the projection are not entities,
and are not associated with the unit of work."

Thanks for the reply Ivan.

I am using projection because I need to use distinct & order. I don't want to do a group by since that would be on the application side and i prefer to keep that logic on the db side (the result set would be massive).

I got the code to syntactically work.. but i think something is still off. Here's my code:

protected void LoadRecentItems()
{
IList<InventoryItemCheckoutHistory> list = new List<InventoryItemCheckoutHistory>();
        Query qry = new Query(typeof(InventoryItemCheckoutHistory));
        qry.Order = Order.By("CreatedOn").Descending();
        qry.Projection.Add("InventoryItemSampleId");
        qry.Distinct = true;
qry.Page = Mindscape.LightSpeed.Querying.Page.Limit(15);

var EL_Context = new LightSpeedContext("EnterpriseLibraryTest");
        using (var uow = EL_Context.CreateUnitOfWork())
        {
IList<InventoryItemCheckoutHistory> RecentInventoryItems = uow.Project<InventoryItemCheckoutHistory>(qry);
                foreach (InventoryItemCheckoutHistory i in RecentInventoryItems)
                {
uow.Add(i);
list.Add(i);
                }
                RecentItemsList.DataSource = list;
                RecentItemsList.DataBind();
}
}

Regardless of whether I order it by Descending() or not I'm still getting the same result back. I just want a list of the last 15 unique InventoryItemSampleId. What am I doing wrong?

Thanks,

SR.

I think there are a couple of issues here.

First, because you are projecting only one field, you should be materialising into a class with only that one field.  Something like:

public class InventoryItemSummary {         // NB not an entity!
  public int InventoryItemSampleId { get; set; }
}

IList<InventoryItemSummary> summaries = uow.Project<InventoryItemSummary>(qry);

Second, the issue about the order not being respected.  This is a database issue.  Here's the word from Jeremy, our database guru:

Its expected. On most databases you can only order by an item if it is in the selection list. In the SQL below you don’t have [CreatedOn] in the select list, so it can’t be ordered on.

I think the database makes this rule because there could be multiple rows with each DISTINCT SampleId value, the ordering of those DISTINCT values isn't well-defined.  You might have { SampleId = 1, CreatedOn = 1/1/2008 }, { SampleId = 2, CreatedOn = 1/1/2009 }, { SampleId = 1, CreatedOn = 1/1/2010 }: now, should SampleId 1 appear before or after SampleId 2?

Obviously, you can get around this by adding CreatedOn to the projection, but that will change the DISTINCT criteria so it will now be possible to have duplicate SampleIds.  I am not sure what the best way is to get the results you need -- it may be that you have to resort to grouping or something like that, or even to client-side code.

Thanks Ivan.

I understand the db limitation on ordering only on fields that are in the SELECT field list, but unfortunately the timestamp makes the InventorySampleId field not so unique anymore. This is an interesting paradox indeed.. I tried to write SQL statement to get the logic of it to work then translate it into LS api. But I jumped ahead and started playing directly in LS to see how it will handle it. I'll go back to working on SQL statement first then translating it. Thanks.