Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> SELECT expression for entity
 

Pages: 1
LLBLGen Pro Runtime Framework
SELECT expression for entity
Page:1/1 

  Print all messages in this thread  
Poster Message
Ian
User



Location:
Hertfordshire, UK
Joined on:
01-Apr-2005 16:37:36
Posted:
511 posts
# Posted on: 03-Sep-2012 23:32:22.  
I want to have an express in a select list which I can then filter on. Is it possible to do this with an entity or do I need to do the DataTable thing?

So the query would look something like this...

SELECT
e.Id,
30 - e.Days As DaysSince
FROM
tbl_entity e
WHERE
DaysSince in (1, 2, 3)

(The 30 would in reality come from a joined table)
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8109 posts
# Posted on: 04-Sep-2012 07:58:31.  
Yes you can, mostly with projections. Here are some options:

Using custom EntityFactory
Code:
public partial class CustomerEncryptEntityFactory : CustomerEntityFactory
{
    public override IEntityFields2 CreateFields()
    {
        var toReturn = base.CreateFields();
        toReturn[(int) CustomerFieldIndex.CompanyName].ExpressionToApply =
            new DbFunctionCall("LEFT", new object[] { CustomerFields.CompanyName, 5 });

        return toReturn;
    }
}
...
public partial class OrderEntity
{
    public decimal Total
    {
        get
        {
            object value = this.Fields[this.Fields.Count - 1].CurrentValue;
            if (value != null)
            {
                return Convert.ToDecimal(value);
            }
            else
            {
                return 0;
            }
        }
    }
}
...
var orders = new EntityCollection<OrderEntity>(new OrderEntityFactoryExtended());
using (var adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, null);
}



Using DynamicList to EntityCollection projection
Code:
// define the dynamic list
var fields = new ResultsetFields(15);
fields.DefineField(OrderFields.OrderId, 0);
fields.DefineField(OrderFields.CustomerId, 1);
...
fields.DefineField(OrderFields.Freight, 14, "Total");

fields[14].ExpressionToApply =            
    new ScalarQueryExpression(OrderDetailFields.UnitPrice
        .SetExpression(new Expression(OrderDetailFields.Quantity, ExOp.Mul, OrderDetailFields.UnitPrice))
        .SetAggregateFunction(AggregateFunction.Sum),
        (OrderFields.OrderId == OrderDetailFields.OrderId));


// define the projection
var orders = new EntityCollection<OrderEntity>();
DataProjectorToIEntityCollection2 projector = new DataProjectorToIEntityCollection2(orders);            
List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
for (int i = 0; i < (fields.Count); i++)
{
    valueProjectors.Add(new DataValueProjector(fields[i].Alias, i, fields[i].DataType));
}
            
// fetch it
using (var adapter = new DataAccessAdapter())
{
    projector = new DataProjectorToIEntityCollection2( orders );
    adapter.FetchProjection( valueProjectors, projector, fields, null, 0, false );
}


Using LINQ2LLBL
Code:
var orders = new List<OrderEntity>();
using (var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    orders = (from o in metaData.Order
                select new OrderEntity
                {
                    OrderId = o.OrderId,
                    CustomerId = o.CustomerId,
                    EmployeeId = o.EmployeeId,
                    Total = o.OrderDetails.Sum(od => od.Quantity * od.UnitPrice)
                }).ToList();
}


Using QuerySpec
Code:
var qf = new QueryFactory();
var q = qf.Order
    .Select(
        OrderFields.OrderId,
        OrderFields.OrderDate,
        OrderFields.CustomerId,
        OrderFields.EmployeeId,
        qf.OrderDetail.CorrelatedOver(OrderDetailEntity.Relations.OrderEntityUsingOrderId)
            .Sum(OrderDetailFields.Quantity * OrderDetailFields.UnitPrice))                                
                                
    .WithProjector(r => new OrderEntity
    {
        OrderId = (int)r[0],
        OrderDate = (DateTime)r[1],
        CustomerId = (string)r[2],
        EmployeeId = (int)r[3],
        Total = (decimal)r[4]
    });

var orders = new List<OrderEntity>();
using (var adapter = new DataAccessAdapter())
{
    orders = adapter.FetchQuery(q);
}


For the last four snippets, you should have a custom property in your entity to fill in:
Code:
public partial class OrderEntity
{
    public decimal Total
    {
        get;
        set;
    }
}


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.