Hello,

I'd like to retrieve a list of an object (type ClientTradeItem), with a properties which is an entity (TradeslibEntity).

Here is my code (a sample is better than a long explanation ) :

var qf = new QueryFactory();
var q = qf.Create()
    .Select
    (() => new ClientTradeItem
    {
        tradeslib = new TradeslibEntity(TradeslibFields.Id.ToValue<int>()),
        riskCustomer = RiskCustomerFields.DisplayName.Trim().ToValue<string>(),
        equityAccountNumber = EquityAccountFields.AccountNumber.Trim().ToValue<string>(),
        equityAccountLabel = EquityAccountFields.AccountLabel.Trim().ToValue<string>(),
    }
    )
    .From
    (
        qf.Tradeslib
            .InnerJoin(TradeslibEntity.Relations.RiskCustomerEntityUsingRiskCustomerOid)
            .InnerJoin(TradeslibEntity.Relations.EquityAccountEntityUsingEquityAccountOid)
    );

My field "tradeslib" is empty, I don't known how to load the data in, could you help me ?

Thanks a lot, Regards, Etienne.

Using llblgen pro 4.1 Final, Framework .Net 4.5 (VS2012) and SqlServer 2012

Hi Etienne,

To help you to write a better query, please explain how the three entities are related (kind of relationship between them).

Hi Daelmo

Tradeslib <-1 - n -> RiskCustomer Tradeslib <-1 - n -> EquityAccount

ClientTradeItem is a new object:

public class ClientTradeItem
{
        #region Properties
        public TradeslibEntity tradeslib { get; set; }
        public string riskCustomer { get; set; }
        public string equityAccountNumber { get; set; }
        public string equityAccountLabel { get; set; }
        #endregion
}

I'd like to get the entire object tradeslib (not only few fields).

Thanks in advance, Etienne.

Hello,

Here is a sample code of what I do :

using (var adapter = adapterTools.GetNewAdapter()) { var qf = new QueryFactory(); var q = qf.Create() .Select (() => new ClientTradeItem { tradeslib = new TradeslibEntity(TradeslibFields.Id.ToValue<int>()), riskCustomer = RiskCustomerFields.DisplayName.Trim().ToValue<string>(), equityAccountNumber = EquityAccountFields.AccountNumber.Trim().ToValue<string>(), equityAccountLabel = EquityAccountFields.AccountLabel.Trim().ToValue<string>() } ) .From ( qf.Tradeslib .InnerJoin(TradeslibEntity.Relations.RiskCustomerEntityUsingRiskCustomerOid) .InnerJoin(TradeslibEntity.Relations.EquityAccountEntityUsingEquityAccountOid) );

var result = adapter.FetchQuery(q);

foreach (var item in result)
{
    adapter.FetchEntity(item.tradeslib);
}

}

Performance are (of course) very slow because I need to load "tradeslib" object one by one (so X query). I'm sure it can be done in one simple query but don't know how.

Thanks

The TradeslibEntity in the projection is now creating a new empty entity with the id set from the one in the From clause set, you want that entity fetched? As you then have to specify a nested query using CorrelatedOver, so the engine will fetch 2 queries: the outer one and the nested one (the TradeslibEntity fetch) and will then merge them at runtime.

You can also do the following (as the Tradeslib entity is already available in the from clause):

using (var adapter = adapterTools.GetNewAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Create()
        .Select
        (() => new ClientTradeItem
        {
            tradeslib = new TradeslibEntity() 
                        { 
                            Id = TradeslibFields.Id.ToValue<int>(),  
                            // rest of the properties you want to set
                        },
            riskCustomer = RiskCustomerFields.DisplayName.Trim().ToValue<string>(),
            equityAccountNumber = EquityAccountFields.AccountNumber.Trim().ToValue<string>(),
            equityAccountLabel = EquityAccountFields.AccountLabel.Trim().ToValue<string>()
        }
        )
        .From
        (
            qf.Tradeslib
                .InnerJoin(TradeslibEntity.Relations.RiskCustomerEntityUsingRiskCustomerOid)
                .InnerJoin(TradeslibEntity.Relations.EquityAccountEntityUsingEquityAccountOid)
        );

    var result = adapter.FetchQuery(q);

    foreach (var item in result)
    {
        adapter.FetchEntity(item.tradeslib);
    }
}

It runs 1 query, not a SELECT N+1 query. Nested queries also don't do a select N+1, but 1 query per node

Hi Otis,

Thanks for your answer, that's what I want to do. Is there a way not to specified all properties (because this object contain around 200 properties) ?

I found this way (after FetchQuery execution) :

var tsList = result.Select<ClientTradeItem, TradeslibEntity>(c => c.tradeslib);
EntityCollection<TradeslibEntity> tsCollection = new EntityCollection<TradeslibEntity>(tsList);
var tse = new TradeslibEntity();
var fields = new ExcludeIncludeFieldsList();
fields.AddRange(tse.Fields);
adapter.FetchExcludedFields(tsCollection, fields);

It fetched all fields without query the server (I assume that datas are already loaded as you say), but in my case it's not perfect because I need the objects filled at one time (problems with events).

Thanks, Regards Etienne.

Hi Etienne,

Yes, you have to specify all the projected fields in the query, afaik. The way you do it in your last post in in-memory, you already fetched all fields and then you are creating your custom object. The other way mentioned by Frans was to put a correlation in the subquery. This is an example using Northwind entities:

var q2 = qf.Order
    .Select
    (() => new ClientTradeItem
    {
        order = qf.Order
            .CorrelatedOver(CustomerEntity.Relations.OrderEntityUsingCustomerId)
            .ToSingleResult<OrderEntity>(),
        employeeName = EmployeeFields.LastName.ToValue<string>(),
        customerName = CustomerFields.CompanyName.ToValue<string>()
    }
    )
    .From
    (
        qf.Order
        .InnerJoin(OrderEntity.Relations.EmployeeEntityUsingEmployeeId)
        .InnerJoin(OrderEntity.Relations.CustomerEntityUsingCustomerId)
    );

Hi Daelmo,

Thanks for your answer

I've try this, but it's very slow because the table is huge (10M records). Specify all the fields is long and if the entity change in the model I have to think of updating my code (and if it's an other developer, he can forget). I've try to make a CorrelatedOver on the same entity, using the primary key but I can't make it works. Something like this :

var q2 = qf.Order
    .Select
    (() => new ClientTradeItem
    {
        order = qf.Order.As("Ord")
            .CorrelatedOver(OrderFields.Id.Source("Ord").Equal(OrderFields.Id))
            .ToSingleResult<OrderEntity>(),
        employeeName = EmployeeFields.LastName.ToValue<string>(),
        customerName = CustomerFields.CompanyName.ToValue<string>()
    }
    )
    .From
    (
        qf.Order
        .InnerJoin(OrderEntity.Relations.EmployeeEntityUsingEmployeeId)
        .InnerJoin(OrderEntity.Relations.CustomerEntityUsingCustomerId)
    );

Maybe be we can get this function in a future version

EtienneKepler wrote:

Hi Daelmo,

Thanks for your answer

I've try this, but it's very slow because the table is huge (10M records).

The single query executed on 10M rows is slow you mean? But isn't it that you're fetching all rows? Without measuring what's slow, it's guesswork... Joining 10M rows would indeed be slow (you do 2 joins). With a very wide table (200+ fields) it's even more slow as a lot of data is loaded and send from the DB to the client.

Specify all the fields is long and if the entity change in the model I have to think of updating my code (and if it's an other developer, he can forget). I've try to make a CorrelatedOver on the same entity, using the primary key but I can't make it works. Something like this :

var q2 = qf.Order
    .Select
    (() => new ClientTradeItem
    {
        order = qf.Order.As("Ord")
            .CorrelatedOver(OrderFields.Id.Source("Ord").Equal(OrderFields.Id))
            .ToSingleResult<OrderEntity>(),
        employeeName = EmployeeFields.LastName.ToValue<string>(),
        customerName = CustomerFields.CompanyName.ToValue<string>()
    }
    )
    .From
    (
        qf.Order
        .InnerJoin(OrderEntity.Relations.EmployeeEntityUsingEmployeeId)
        .InnerJoin(OrderEntity.Relations.CustomerEntityUsingCustomerId)
    );

Maybe be we can get this function in a future version

You should use

    order = qf.Order.**TargetAs**("Ord")

instead of 'As'. This is a bit confusing, but there are two alias specification statements: As and TargetAs. As is for aliasing a query or fragment in a Join, TargetAs is to specify the alias of a target inside a query. If there are no other elements appended to the element, e.g. in a join you have this situation, TargetAs and As are equal. In the case of your query, you append a where clause, so the 'As' will alias the whole query (as the Where is going to be part of qf.Order!) but you want to alias the target of Order inside the query.

When doing this, the engine will execute two queries: one for the outer query without the nested entity fetch and one for the nested entities and will merge them at runtime.

Thanks Otis,

Yes the single query executed on 10M is very slow (it's normal of course), when I'm looking into ORM Profiler, it execute the query without filter and I guess the joining will be done after : - First query -> Retrieve the various datas (~150 rows) - Second query -> Retrieve the 10M rows data without filter - Join between first and second query --> Using this is not the good way, TargetAs is much better.

Also, the TargetAs work fine but it's slower than using the FetchExcludedFields. In my case (Connection#1 in attachment) : TargetAs : 3 queries, 3.1 seconds FetchExcludedFields : 3 queries, 1 second

Finally I'll keep the FetchExcludedFields, it's not the cleaner, but it's the faster. The best way should be mapping projection fields without naming each fields like :

tradeslib = new TradeslibEntity(TradeslibEntity.Fields),

By the way, thanks a lot for all your answers, appreciate

EtienneKepler wrote:

Thanks Otis,

Yes the single query executed on 10M is very slow (it's normal of course), when I'm looking into ORM Profiler, it execute the query without filter and I guess the joining will be done after : - First query -> Retrieve the various datas (~150 rows) - Second query -> Retrieve the 10M rows data without filter - Join between first and second query --> Using this is not the good way, TargetAs is much better.

Also, the TargetAs work fine but it's slower than using the FetchExcludedFields. In my case (Connection#1 in attachment) : TargetAs : 3 queries, 3.1 seconds FetchExcludedFields : 3 queries, 1 second

Yes, that's about right. It has to execute the query again to filter out the child nodes, which indeed will take longer than just 1 query.

Finally I'll keep the FetchExcludedFields, it's not the cleaner, but it's the faster. The best way should be mapping projection fields without naming each fields like :

tradeslib = new TradeslibEntity(TradeslibEntity.Fields),

We added this in v4.2, it will take 1 method call to produce a typed projector so no more long lists of fields to type

By the way, thanks a lot for all your answers, appreciate

no problem