SELECT expression for entity

Posts   
 
    
Ian avatar
Ian
User
Posts: 511
Joined: 01-Apr-2005
# 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)

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 04-Sep-2012 07:58:31   

Yes you can, mostly with projections. Here are some options:

Using custom EntityFactory

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

// 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

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

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:

public partial class OrderEntity
{
    public decimal Total
    {
        get;
        set;
    }
}
David Elizondo | LLBLGen Support Team