Query Object complex projection

Using Linq I can do something similar to the following example where I join three tables and pass the entities to my method MapToMyObjectType( ) which combines information from the three entities and produces a resultant MyObjectType object. Is it possible to do something like this using a Query object instead of Linq?

List<MyObjectType> result = (from cocust in uow.DbEntityA
join acct in uow.DbEntityB on cocust.PayoracctId equals acct.Id
join cust in uow.DbEntityC on acct.CustaccnumberaccId equals cust.Id
where cocust.OwnerId == contractId
orderby cocust.Id descending
select MapToMyObjectType(cocust, acct, cust).ToList( );

I'm thinking that maybe it can be done via Projection somehow but I've not had much luck using projection with Query object as shown in example I found, e.g.

struct MyProjectionResult
{
                public int ContributorId { get; set; }
                public int ApprovedById { get; set; }
}
var query = new Query(typeof(Contribution));
query.Projection.Add("ContributorId");
query.Projection.Add("ApprovedById")
var results = unitOfWork.Project<MyProjectionResult>(query);

Specifying the column names doesn't see to work for me. Will the column names work even though there seems to be some aliasing that goes in the generated SQL using "AS". In other words, if I specify a column name such as "EndDate" from the SQL snippet below the projection doesn't work.

    SELECT
      CoCustomerAcctEntry.Id AS "id_0",
      CoCustomerAcctEntry.EndDate AS "enddate_0",
      CoCustomerAcctEntry.IsEndPair AS "isendpai_0",
      CoCustomerAcctEntry.OrdereracctId AS "orderera_0",
      CoCustomerAcctEntry.OvrdContractEnd AS "ovrdcont_0",
      CoCustomerAcctEntry.OvrdContractStart AS "ovrdcont_1",
      CoCustomerAcctEntry.OwnerId AS "ownerid_0",
      CoCustomerAcctEntry.PayoracctId AS "payoracc_0",
      CoCustomerAcctEntry.RecvContractDocs AS "recvcont_0",
      CoCustomerAcctEntry.StartDate AS "startdat_0"

If you are using the ProjectionCollection on the Query object then you need to specify the property names rather than column names. Can you elaborate on what is not working when you specify EndDate? Because you are using more than one entity type in the query make sure you specify the entity type as part of the projection which will help LightSpeed understand which type you want to be used for that projection element.

The example you have in LINQ is actually performing the projection client side, so LightSpeed will be materialising your full entities and then you are making a method call to MapToMyObjectType for each group of objects returned in the query.

To do something similar with the Query object you can use FindGroup which will return an EntityTuple with the entities materialized per row and then you can iterate over those results and make your method call in a similar fashion.

Jeremy

Hi Jeremy,
Thanks for the information.

Here's a simple example of the problem I ran into with projection. I have an Entity DbCocustomerAcctEntry and if I look at the .cs file generated for that entity from the lightspeed model, it has the following property for the column I'm interested in.

/// <summary>Gets or sets the ID for the <see cref="Payoracct" /> property.</summary>
[System.Diagnostics.DebuggerNonUserCode]
public System.Nullable<int> PayoracctId
{
  get { return Get(ref _payoracctId, "PayoracctId"); }
  set { Set(ref _payoracctId, value, "PayoracctId"); }
}

I if use the following struct...

public struct CustomerTest
{
public int PayoracctId { get; set; }
}

...I would expect the following projection to work, but it doesn't. I get a list of results back, but the value of PayoracctId is 0 for every one. However if I change the name of the property in the struct from "PayoracctId" to "payoracc_0" to match the SQL that was genereated (see below) then I DO get values back instead of 0. Any idea why?

            Query test = new Query(typeof(DbCocustomerAcctEntry))
            {
                QueryExpression = Entity.Attribute(DbCocustomerAcctEntry.OwnerIdField) == contractId
            };
            test.Projection.Add("PayoracctId");
            IList<CustomerTest> results = uow.Project<CustomerTest>(test).ToList();

SELECT
CoCustomerAcctEntry.PayoracctId AS "payoracc_0"
FROM
CoCustomerAcctEntry
WHERE
CoCustomerAcctEntry.OwnerId = 95383;
END;

Any idea why the previous example does not work like the Projection example given in the Lightspeed docs using the property name? I don't want to have to inspect the generated SQL to know what the alias is ...

This looks to be a bug in the way this is being handled when provider column aliasing is being used (Im assuming you are using Oracle?). Ill need to look into this to understand what is going on and see if we can get this fixed and I will let you know once we have an update on this.

Jeremy

Yes, that is correct, I am using ORACLE. Thanks for the follow up and confirmation of possible problem as I was not seeing what I was doing wrong. Please advise when you have any additional information. Thanks