Saturday, June 16, 2012

Frictionless object graph n-tier using ServiceStack, DitTO and ToTheEfnhX

The subtitle is: It works on both Entity Framework and NHibernate!


Ok, there's too much plugging there :-) Though definitely you should be using ServiceStack and use whatever DTO mapper and repository pattern that suits your need. I highly recommend DitTO POCO-DTO mapper and ToTheEfnhX repository pattern ;-)


First of all, we start with the foundation. Do we really need to transport the domain objects as it is? The concensus is not. Whatever the entities of the business is, it should not spill to the consuming side(e.g. jQuery, Winforms, WPF, Silverlight, etc).


Then the second thing we need to consider, take the following classic example of Order and OrderLines object relationship. The OrderLine has a reference on its parent object, a circular reference.


public class Order
{
    public virtual int OrderId { get; set; }

    public virtual Customer Customer { get; set; }
    public virtual string OrderDescription { get; set; }
    public virtual DateTime OrderDate { get; set; }
   
    public virtual IList<OrderLine> OrderLines { get; set; }

    public virtual byte[] RowVersion { get; set; }
}

public class OrderLine
{
    public virtual Order Order { get; set; }

    public virtual int OrderLineId { get; set; }

    public virtual Product Product { get; set; }
    public virtual int Quantity { get; set; }
    public virtual decimal Price { get; set; }
    public virtual decimal Amount { get; set; }
    public virtual Product Freebie { get; set; }

    public virtual IList<Comment> Comments { get; set; }
}

public class Comment
{
    public virtual OrderLine OrderLine { get; set; }

    public virtual int CommentId { get; set; }
    public virtual string TheComment { get; set; }
}



Some services can not transport the Order when its OrderLine has a circular reference to the same Order object. Though you can solve it on services such as WCF by putting [DataContract(IsReference=true)] on your Order class, but recently I came to the conclusion that this property need not be present on the other side of the wire(client), especially on n-tier app. Object references (or Independent Association in Entity Framework's parlance) are just an ORM artifacts for persisting objects.



With that in mind, we can just return DTOs devoid of circular reference.


public class OrderDto
{
    public int OrderId { get; set; }

    public int CustomerId { get; set; }
    public string OrderDescription { get; set; }
    public DateTime? OrderDate { get; set; }
    
    public List<OrderLineDto> OrderLines { get; set; }

    public byte[] RowVersion { get; set; }
}


public class OrderLineDto
{
    public int OrderLineId { get; set; }

    public int ProductoId { get; set; }
    public string ProductDescription { get; set; }

    public int FreebieId { get; set; }

    public int Quantity { get; set; }
    public decimal Price { get; set; }
    public decimal Amount { get; set; }

    public List<CommentDto> Koments { get; set; }
}



public class CommentDto
{
    public int CommentId { get; set; }
    public string TheComment { get; set; }
}


Besides manually mapping all the corresponding selected values(ProductoId, yes it's ProductoId, later we will demonstrate how to override mapping with DitTO), we still have one hurdle to tackle before we can map back the DTO to its POCO counterpart. How can we map the OrderLine back to the parent?


If we will do the mapping manually, we shall do this:

public override object OnPost(OrderRequest request)
{
    Order o = new Order
    {
        OrderId = request.OrderDto.OrderId,
        Customer = LoadStub(request.OrderDto.CustomerId),
        OrderDescription = request.OrderDto.CustomerId,
        OrderDate = request.OrderDto.OrderDate,
        RowVersion = request.OrderDto.RowVersion
        OrderLines = new List<OrderLine>()
    };
    
    foreach(OrderLineDto olx in request.OrderDto.OrderLines)
    {
        var ol = new OrderLine
        {
            Order = o, // this link the OrderLine to its parent Order. So an ORM won't have a problem persisting the object
            
            Product = LoadStub(olx.ProductoId),
            Freebie = LoadStub(olx.FreebieId),            
            Quantity = olx.Quantity,
            Price = olx.Price,
            Amount = olx.Amount
        };
        
        o.OrderLines.Add(ol);
    }
    
    Repository.SaveGraph(o);        
}


We aren't expecting humans to type those things over and over, right? Besides, we also need to map the Comments on each OrderLine. In our model, an OrderLine is one-to-many with Comments, that's a contrived example, but will do for the purpose of demonstrating why we need a decent DTO mapper that can map the POCO to DTO and vice versa automatically, a mapping library that can map object graph birectionally.

Linking the object reference between collection and its parent is a very involved code too. Manual mapping is prone to error if you forgot what properties needed be assigned.


With this in mind, I've made an object-mapper that can spare the devs on the drudgeries of individually assigning properties and linking the child objects to its parent. And this DTO mapper works bidirectionally too, it can map back the DTO to POCO. Let's cut to the chase and see how the mapping looks like:


class OrderMapping : DtoPocoMap<OrderDto, Order>
{
    public override void Mapping()
    {
        MapKey(d => d.CustomerId, s => s.Customer.CustomerId);
                    
        MapList(d => d.OrderLines, s => s.OrderLines, r => r.Order);
    }
}

class OrderLineMapping : DtoPocoMap<OrderLineDto, OrderLine>
{
    public override void Mapping()
    {
        MapKey(d => d.ProductoId, s => s.Product.ProductId);
        Map(d => d.ProductDescription, s => s.Product.ProductDescription);
        MapKey(d => d.FreebieId, s => s.Freebie.ProductId);

        MapList(d => d.Koments, s => s.Comments, r => r.OrderLine);
    }
}


You might notice that we didn't include Price,Qty and Amount on OrderLineMapping. If the names matches, there's no need for us to override the mapping, it's automatically done when the name matches. Likewise on Order class, the OrderId, OrderDate and OrderDescription was not mapped manually. Only the CustomerId is mapped manually, as the CustomerId is not directly accessible from the Order object. Likewise with Comments class, there's no need to override mapping for the whole CommentDto class as all its properties' names exactly matches its corresponding POCO. The only explicit mappings needed be done are:

1) when the names doesn't matches

2) when the scalar value from POCO side is in another object reference, e.g. MapKey(x => x.CustomerId, y => y.Customer.CustomerId);

3) when you have a list to map, e.g. MapList(x => x.OrderLines, x => x.OrderLines, z => z.Order);


Explicit mapping of list is mandatory, as there are potential problems if the mapper try to infer the object reference that should be matched from child objects against the parent object. Explicit list mapping indicates what maps the child objects to their parent object, that indicator is the third parameter on MapList. e.g. MapList(x => x.OrderLines, x => x.OrderLines, z => z.Order);


To deliver a frictionless n-tier code, the mapping between POCO to DTO should be a simple undertaking, and a one-liner code at that. An example of getting the object graph:

public override object OnGet(CustomerRequest request)
{
    return new CustomerRequestResponse
    {
        CustomerDto = Ienablemuch.DitTO.Mapper.ToDto<Customer, CustomerDto>(Repository.Get(request.Id))
    };
}


An example of receiving back an object graph and mapping it back to POCO:

public override object OnPost(OrderRequest request)
{
    Order poco = Ienablemuch.DitTO.Mapper.ToPoco<OrderDto, Order>(request.OrderDto);
    // NHibernate don't have problem with stub references. Entity Framework stub object needed be wired to an object already in identity maps
    // http://en.wikipedia.org/wiki/Identity_map_pattern            
    Repository.AssignStub(poco);

    Repository.SaveGraph(poco);
    return new OrderRequestResponse { OrderDto = new OrderDto { OrderId = poco.OrderId, RowVersion = poco.RowVersion } };
}

Saving back the object is supposedly just a two-liner code, but there's something fundamentally different how Entity Framework's stub object operates. When an stub object identity matches an object that has the same id in Entity Framework's object state tracker, stub objects needed to reference those live objects instead, this is called Identity Map Pattern. So for that EF's eccentricity, we have to assign live object for stubs that matches the identity map before calling the Repository.SaveGraph, for this we use the AssignStub extension method. By the way, it's ok to keep the AssignStub code even we are using NHibernate, AssignStub will not do anything if we are using NHibernate, NHibernate automatically maps the stub objects to its identity maps.

So for saving objects, we have three steps.

First, we map back the DTO to a POCO object:

Order poco = Ienablemuch.DitTO.Mapper.ToPoco<OrderDto, Order>(request.OrderDto);

Second, we invoke AssignStub:

Repository.AssignStub(poco);

Then finally, we call the SaveGraph:

Repository.SaveGraph(poco);

Now on the client-side of things. We invoke ServiceStack JsonServiceClient's Post method when saving.

void uxSave_Click(object sender, EventArgs e)
{

    OrderRequestResponse r = null;
    try
    {
        OrderDto dto = (OrderDto)bdsOrder.Current;                               
        r = Client.Post<OrderRequestResponse>(address + "/order_request", new OrderRequest { OrderDto = dto });

        dto.OrderId = r.OrderDto.OrderId;
        dto.RowVersion = r.OrderDto.RowVersion;

        bdsOrder.ResetItem(0);
        MessageBox.Show("Saved.");
    }    
    catch (Exception ex)
    {
        if (r != null)
        {
            MessageBox.Show(r.ResponseStatus.StackTrace);
        }
        MessageBox.Show(ex.Message);
    }    
}


When opening, we use ServiceStack JsonServiceClient's Get method

private void uxOpen_Click(object sender, EventArgs e)
{
    try
    {
        int id = int.Parse(uxLoadId.Text);

        OpenOrder(id);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

void OpenOrder(int id)
{
    var r = Client.Get<OrderRequestResponse>(address + "order_request/" + id);
    bdsOrder.DataSource = r.OrderDto;
}


On infrastructure side of things, we put these routings on ServiceStack AppHost's Configure method:


Routes
    .Add<OrderRequest>("/order_request")
    .Add<OrderRequest>("/order_request/{Id}")
    .Add<OrderRequest>("/order_request/{Id}/{RowVersion}");

Routes
    .Add<CustomerRequest>("/customer_request")
    .Add<CustomerRequest>("/customer_request/{Id}");

Routes
    .Add<ProductRequest>("/product_request")
    .Add<ProductRequest>("/product_request/{Id}");


We also put this one-time initialization of DTO and POCO mappings:

Ienablemuch.DitTO.Mapper.FromAssemblyOf<TheServiceStack.DtoPocoMappings.OrderMapping>();


Then on dependency-injected repository objects, setup the following:


#if UseEF

    container.Register<System.Data.Entity.DbContext>(x => new TheServiceStack.DbMappings.EfDbMapper(connectionString)).ReusedWithin(Funq.ReuseScope.Container);

    
    container.Register<Ienablemuch.ToTheEfnhX.IRepository<TheEntities.Poco.Order>>(c =>
        new Ienablemuch.ToTheEfnhX.EntityFramework.Repository<TheEntities.Poco.Order>(
            c.Resolve<System.Data.Entity.DbContext>())).ReusedWithin(Funq.ReuseScope.None);
            
    container.Register<Ienablemuch.ToTheEfnhX.IRepository<TheEntities.Poco.Product>>(c =>
        new Ienablemuch.ToTheEfnhX.EntityFramework.Repository<TheEntities.Poco.Product>(
            c.Resolve<System.Data.Entity.DbContext>())).ReusedWithin(Funq.ReuseScope.None);
        
    container.Register<Ienablemuch.ToTheEfnhX.IRepository<TheEntities.Poco.Customer>>(c =>
        new Ienablemuch.ToTheEfnhX.EntityFramework.Repository<TheEntities.Poco.Customer>(
            c.Resolve<System.Data.Entity.DbContext>())).ReusedWithin(Funq.ReuseScope.None);

#else

    container.Register<NHibernate.ISession>(x => 
        TheServiceStack.DbMappings.NHMapping.GetSession(connectionString)).ReusedWithin(Funq.ReuseScope.Container);
    
    
    container.Register<Ienablemuch.ToTheEfnhX.IRepository<TheEntities.Poco.Order>>(c =>
        new Ienablemuch.ToTheEfnhX.NHibernate.Repository<TheEntities.Poco.Order>(
            c.Resolve<NHibernate.ISession>())).ReusedWithin(Funq.ReuseScope.None);            
            
    container.Register<Ienablemuch.ToTheEfnhX.IRepository<TheEntities.Poco.Product>>(c =>
        new Ienablemuch.ToTheEfnhX.NHibernate.Repository<TheEntities.Poco.Product>(
            c.Resolve<NHibernate.ISession>())).ReusedWithin(Funq.ReuseScope.None);
        
    container.Register<Ienablemuch.ToTheEfnhX.IRepository<TheEntities.Poco.Customer>>(c =>
        new Ienablemuch.ToTheEfnhX.NHibernate.Repository<TheEntities.Poco.Customer>(
            c.Resolve<NHibernate.ISession>())).ReusedWithin(Funq.ReuseScope.None);
#endif



We don't use singleton, there are funny things that are happening with singleton objects, though unfortunately is the Funq dependency injection's default object instance injection mechanism. We should get a new object everytime for repositories, for this we need to pass a parameter of Funq.ReuseScope.None to ReusedWithin. For ORM connection(Entity Framework's DbContext and NHibernate's ISession) we use Funq.ReuseScope.Container, this is not a singleton, you still get new ORM connection on every invocation of the service. If you use Funq.ReuseScope.None on ORM connection too, not only you get a new connection every service invocation, each repository will have their own ORM connection; however, that has a problem, that prevents joining queries manually from the different repositories. An example:


public class OrderRequestService : ServiceStack.ServiceInterface.RestServiceBase<OrderRequest>
{

    // dependency injected properties    
    public Ienablemuch.ToTheEfnhX.IRepository<Order> RepositoryO { get; set; }
    public Ienablemuch.ToTheEfnhX.IRepository<OrderLine> RepositoryOL { get; set; }
    

    OrderRequestResponse Search(OrderRequest request)
    {
        var query = RepositoryO.All;

        if (request.OrderDto.CustomerId != 0)
        {
            query = query.Where(x => x.Customer.CustomerId == request.OrderDto.CustomerId);
        }

        if (request.OrderDto.OrderDate != null)
        {
            query = query.Where(x => x.OrderDate >= request.OrderDto.OrderDate);
        }

        if (!string.IsNullOrEmpty(request.OrderDto.OrderDescription))
        {
            query = query.Where(x => x.OrderDescription.Contains(request.OrderDto.OrderDescription));
        }

        if (request.OrderDto.OrderLines.Any())
        {
            foreach (OrderLineDto o in request.OrderDto.OrderLines)
            {

                IQueryable<OrderLine> olFilter = RepositoryOL.All;

                if (o.ProductoId != 0)
                {
                    int pId = o.ProductoId;
                    olFilter = olFilter.Where(x => x.Product.ProductId == pId);
                }

                if (o.Quantity != 0)
                {
                    int qty = o.Quantity;
                    olFilter = olFilter.Where(x => x.Quantity == qty);
                }

                
                // This will have a runtime error if we use Funq.ReuseScope.None on ORM's connection. As the two IQueryable don't share the same connection.
                // To make the dependency injected repositories share the same connection, their ORM connection should come from one object reference only, 
                // for this we use Funq.ReuseScope.Container for the ORM's connection.
                query = query.Where(x => olFilter.Any(y => y.Order.OrderId == x.OrderId));
                
            }//foreach
        }//if
        
        ...
    }
}



So in order to allow sharing of connection between repositories, use Funq.ReuseScope.Container. Again, that is not singleton.


So that's all, to paraphrase jQuery mantra: "write less, do more."


The complete code can be found here: https://github.com/MichaelBuen/DemoServiceStackNtier

Another point of interest, ServiceStack fully advocates Martin Fowler DTO pattern: https://github.com/ServiceStack/ServiceStack/blob/master/README.md


If you don't recognize the importance of having a DTO pattern, consider the following flexible search screenshot. Yes that is a search functionality, the data entry doubles as a search filter (ala-Filemaker):





How would you name that search mechanism in WCF(which is an anti-pattern of DTO), with WCF you tend to name your search like this:

Order[] GetAllOrders();
Order GetOrderById(int id);
Order[] GetOrdersByCustomerId(int customerId);
Order[] GetOrdersByOrderDescription(string orderDescription);
Order[] GetOrdersByOrderDate(DateTime orderDate);
Order[] GetOrdersByCustomerAndOrderDate(int customerId, DateTime orderDate);
Order[] GetOrdersByCustomerAndOrderDescription(int customerId, string orderDescription, DateTime orderDate);
Order[] GetOrdersByCustomerAndOrderDescriptionAndOrderDate(int customerId, string orderDescription, DateTime orderDate);
Order[] GetOrdersByCustomerAndOrderDescriptionAndOrderDateAndProductAndQty(int customerId, string orderDescription, DateTime orderDate);
Order[] GetOrdersByCustomerAndOrderDateAndProductAndQty(int customerId, DateTime orderDate, int productId, int Qty);
Order[] GetOrdersByProductAndQty(int productId, int qty);
Order[] GetOrdersByQty(int qty);
Order[] GetOrdersByProduct(int productId);


Heaven forbid what WCF method name and parameters a dev will come up with the search screenshot above; and on top of that, the user might combine other filter(s) (he/she might add any or all of the Order Description filter, Date, Product, Quantity, Prduct+Quantity etc) on that search functionality, what method name a dev will come up that accepts different combinations of filters on WCF?


Life is short, use DTO, use a service stack that embraces it fully, use ServiceStack!


Source codes:


Here's a primer on setting up ServiceStack infrastructure under two minutes: http://www.youtube.com/watch?v=a3HzSvUCbeI

Complete demo code: https://github.com/MichaelBuen/DemoServiceStackNtier

Linq is the new Stored Procedure

After reading Rob Conery's http://blog.wekeroad.com/2009/06/11/youre-not-your-data-access And seeing this Aaron Bertrand's comment on stackoverflow:

Then I suspect brainwashing may have been involved


I believe that .NET developers should practice Buddhism.

Most .NET developers don't know the middle path. If they chosen Stored Procedures, they will decidedly use stored procedures 100% exclusively, SP foreva! to the detriment on excluding other sound techniques. They forgot to be pragmatic, overlooking the fact that some solutions are meant to be solved in a certain way. If you have flexible query filters, instead of making a stored procedure with gazillion parameters, it's better you use techniques or DAL that is a perfect fit for that. Linq perfectly fits the bill and it is type-safe to boot, as it allows you to chain filters in any way you deem fit, and consequently, making the system performant.


...
var query = MemberRepository.All;

if (lastnameFilter != "")
    query = query.Where(x => x.Lastname == lastnameFilter);
 
if (firstnameFilter != "")
    query = query.Where(x => x.Firstname == firstnameFilter);
 
if (memberSinceFilter != 0)
    query = query.Where(x => x.YearMember >= memberSinceFilter);
 
if (pointsFilter != 0)
    query = query.Where(x => x.Points >= pointsFilter);
 

return query.ToList(); 
... 


If you only have one filter, example on points only, that would generate this short query only:

select * 
from Member 
where Points >= 10000;

Whereas if you use stored procedure, this is how that will be tackled:


create procedure MemberSearch(@Lastname varchar(50), @Firstname varchar(50), @MemberSince int, @Points int)
as

select * 
form Member
where (@Lastname = '' OR Lastname = @Lastname)
      AND (@Firstname = '' OR Firsname = @Firstname)
   AND (@MemberSince = 0 OR YearMember >= @memberSince)
   AND (@Points = 0 OR Points >= @Points)

go


I would hazard a guess, most prefers the Linq approach now, as it produces a performant query. If you filter only on specific column(s), other columns will not be included in the query. Linq is my preferred approach too.


Sadly though, even how awesome Linq is, I'm feeling there's a new Stored Procedure in town. Linq is the new Stored Procedure! Don't be confused I'm praising Linq one second and bashing it another. Let me tell you the premise of this article.

Upon seeing this question: http://stackoverflow.com/questions/11058330/select-collection-of-entities-based-on-value-of-most-recent-related-entity

And seeing this comments:

This is close to the output I am looking for. I want where the most recent action is an open, not the most recent open, but to get that, I would just have to move the Status = Open condition to the second select. The bigger issue, though, is that I am being "strongly encouraged" to use Entity to communicate with the DB instead of directly querying it. – user1459547

For what purpose? If it is harder to make EF generate the query you need, and you know how to do it in SQL... – Aaron Bertrand

I am entirely on your side, but I can't convince the project manager. – user1459547

Then I suspect brainwashing may have been involved... – Aaron Bertrand


It eludes me why the need to impose rules or be dogmatic that all data access should be in Linq, when a straightforward SQL is as readable and more performant than Linq-generated SQL.

I would rather see this operate in production:

;WITH MostRecentActions AS 
(
  SELECT RequestID, ActionTimestamp,
    /* other relevant RequestAction columns, */
    rn = ROW_NUMBER() OVER (PARTITION BY RequestID ORDER BY ActionTimestamp DESC)
  FROM dbo.RequestActions
  WHERE Status = 'Open'
)
SELECT RequestID, ActionTimestamp /* , other columns */ 
  FROM MostRecentActions
  WHERE rn = 1;      


Than this complex Linq-generated one:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[SomeProp1] AS [SomeProp1], 
[Extent1].[SomeProp2] AS [SomeProp2], -- ...etc.
FROM  [dbo].[Requests] AS [Extent1]
CROSS APPLY  (SELECT TOP (1) [Project1].[Status] AS [Status]
    FROM ( SELECT 
           [Extent2].[Status] AS [Status], 
           [Extent2].[ActionTimestamp] AS [ActionTimestamp]
           FROM [dbo].[RequestActions] AS [Extent2]
           WHERE [Extent1].[Id] = [Extent2].[RequestId]
    ) AS [Project1]
    ORDER BY [Project1].[ActionTimestamp] DESC ) AS [Limit1]
WHERE N'Open' = [Limit1].[Status]


I have an inkling that code is slow compared to the first one(the CTE approach). Ok, an astute dev might actually profile and try these two codes and may found that the latter code is faster, but that is besides the point. The point is why should we impose dogmatic rules, when you can choose an approach that you can implement quickly and works. Sure, some Linq problems are just an stackoverflow away, but don't forget that you can still drop to bare metal SQL if you want to have more confidence the solution works and as smoothly as you wanted them be. Don't be overzealous on a given platform or framework, don't say "Stored Procedure Forever!" Don't say "Stored Procedure is Dead! Long Live Linq!"


Everything should be in moderation, don't be overzealous on one choice only. Programmers hate -ism, but let me take this as an exception, programmers should sometimes practice Buddhism


In line with pragmatism and to avoid becoming overzealous on certain decisions, the only sound bite that should be tolerated is:

Sound bites considered harmful

Context here: http://blog.securemacprogramming.com/?p=462

Sunday, June 10, 2012

Get Entity Framework class' primary key property names during runtime

The runtime version of getting class' primary key property names ...

[TestMethod]
public void Test_Ef_Get_PK()
{
    // Arrange
    DbContext db = new EfDbMapper(connectionString);
    var objectContext = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)db).ObjectContext;
    System.Data.Objects.ObjectSet<Product> set =
                       objectContext.CreateObjectSet<Product>();

    //Act
    IEnumerable<string> keyNames = set.EntitySet.ElementType.KeyMembers.Select(k => k.Name);

    // Assert
    Assert.AreEqual("ProductId", string.Join(",", keyNames.ToArray()));

}


...is this:

[TestMethod]
public void Test_Ef_Get_PK_via_pure_reflection()
{
    // Arrange
    DbContext db = new EfDbMapper(connectionString);

    var objectContext = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)db).ObjectContext;
    Type t = typeof(Product); // anything that can be obtained during runtime

    MethodInfo m = objectContext.GetType().GetMethod("CreateObjectSet", new Type[] { });
    MethodInfo generic = m.MakeGenericMethod(t);            
    object set = generic.Invoke(objectContext, null);

    PropertyInfo entitySetPI = set.GetType().GetProperty("EntitySet");
    System.Data.Metadata.Edm.EntitySet entitySet = (System.Data.Metadata.Edm.EntitySet) entitySetPI.GetValue(set, null);


    // Act 
    IEnumerable<string> keyNames = entitySet.ElementType.KeyMembers.Select(k => k.Name);


    // Assert
    Assert.AreEqual("ProductId", string.Join(",", keyNames.ToArray()));
}

Avoid stringly-typed, do the .NET proper, use strongly-typed

When you want to do this during run-time...
System.Collections.Generic.IList<Product> products = new System.Collections.Generic.List<Product>();



...instead of doing this:

...
Type elemType = ...
IList clonedList = (IList)Common.Create("System.Collections.Generic.List", elemType);
...

static class Common
{
    internal static object Create(string name, params Type[] types)
    {
        string t = name + "`" + types.Length;
        Type generic = Type.GetType(t).MakeGenericType(types);
        return Activator.CreateInstance(generic);
    }
}


Do this:
Type elemType = ...
IList clonedList = (IList) Activator.CreateInstance(typeof(System.Collections.Generic.List<>).MakeGenericType(elemType));




Happy Coding! ツ

Tuesday, June 5, 2012

Counting properly with database Linq/lambda

This query is not translated to back-end version, materializing any of the class in a linq expression will result in all dependent objects being materialized to in-memory objects, and that includes the list

db.Set<Order>().Single(x => x.OrderId == 1).Customer.Country.Languages.Count

To make that efficient, do that query in this manner. Do note that none of the classes are materialized to an object:

int c = (from x in db.Set<Order>()
         where x.OrderId == 1
         from l in x.Customer.Country.Languages
         select l).Count();


Here's the query generated by that:
SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
 COUNT(1) AS [A1]
 FROM   (SELECT [Extent1].[Customer_CustomerId] AS [Customer_CustomerId]
  FROM [dbo].[Order] AS [Extent1]
  WHERE 1 = [Extent1].[OrderId] ) AS [Filter1]
 CROSS APPLY  (SELECT [Extent2].[AssocCountryId] AS [AssocCountryId]
  FROM  [dbo].[LanguageAssocCountry] AS [Extent2]
  INNER JOIN [dbo].[Customer] AS [Extent3] ON [Extent3].[Country_CountryId] = [Extent2].[AssocCountryId]
  WHERE [Filter1].[Customer_CustomerId] = [Extent3].[CustomerId] ) AS [Filter2]
)  AS [GroupBy1]


If you want to simplify the Linq, don't do it this way, this will materialize the dependent object and rows, as the very first object(via Single) was materialized already, not efficient:

int c = (from l in db.Set<Order>().Single(x => x.OrderId == 1).Customer.Country.Languages
         select l).Count()


To shorten it up a bit, do it this way, none of the classes is materialized to object, this is efficient:
int c = (from x in db.Set<Order>().Where(x => x.OrderId == 1)
         from l in x.Customer.Country.Languages
         select l).Count()

Query-wise, that code generates exactly the same query as the first Linq code of this post.

If you want to write it in pure lambda approach, use the following code. This code also generates the exact same query of the second Linq code of this post:
int c = db.Set<Order>().Where(x => x.OrderId == 1)
          .SelectMany(x => x.Customer.Country.Languages).Count();


This is the generated query when you use NHibernate ( just change the db.Set<Order>() to db.Query<Order>() ):

exec sp_executesql 
N'select cast(count(*) as INT) as col_0_0_ 
from [Order] order0_ 
inner join [Customer] customer1_ on order0_.Customer_CustomerId=customer1_.CustomerId 
inner join [Country] country2_ on customer1_.Country_CountryId=country2_.CountryId 
inner join LanguageAssocCountry languages3_ on country2_.CountryId=languages3_.AssocCountryId 
inner join [Language] language4_ on languages3_.AssocLanguageId=language4_.LanguageId 
where order0_.OrderId=@p0',N'@p0 int',@p0=1