My question is somewhat related to the thread at:

http://llblgen.com/TinyForum/Messages.aspx?ThreadID=14157

I had been struggling with the same issue: some way to create resusable code for mapping entity objects to my domain objects. At one point, I had come upon a similar approach using Expressions and Funcs to encapsulate the mapping logic. I eventually began running into stack overflows as well and due to time constraints gave up on the approach and just duplicated my mapping code. Once I saw this recent, related thread, I decided to just write up some quick test code to prototype something against the updated runtime libraries.

I am running what I believe is the latest version of LLBLGen application (7/22?) and grabbed updated runtime libraries from the forums as follows:

Linq Support: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=78990&ThreadID=14189

ORM Support:

http://llblgen.com/TinyForum/Messages.aspx?ThreadID=14152

The version on the DLL files is listed as 2.6.8.828.

DDL for the related tables:

CREATE TABLE [dbo].[Category](
    [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF__Category__Id__0BC6C43E]  DEFAULT (newsequentialid()),
    [Name] [nvarchar](255) NOT NULL,
    [Description] [ntext] NOT NULL,
    [UrlId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE TABLE [dbo].[Product](
    [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF__Product__Id__07F6335A]  DEFAULT (newsequentialid()),
    [Name] [nvarchar](255) NOT NULL,
    [Description] [ntext] NULL,
    [UrlId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE TABLE [dbo].[ProductCategoryMap](
    [CategoryId] [uniqueidentifier] NOT NULL,
    [ProductId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_ProductCategoryMap_1] PRIMARY KEY CLUSTERED 
(
    [CategoryId] ASC,
    [ProductId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ProductCategoryMap]  WITH CHECK ADD  CONSTRAINT [FK_ProductCategoryMap_Category] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Category] ([Id])
GO
ALTER TABLE [dbo].[ProductCategoryMap] CHECK CONSTRAINT [FK_ProductCategoryMap_Category]
GO
ALTER TABLE [dbo].[ProductCategoryMap]  WITH CHECK ADD  CONSTRAINT [FK_ProductCategoryMap_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([Id])
GO
ALTER TABLE [dbo].[ProductCategoryMap] CHECK CONSTRAINT [FK_ProductCategoryMap_Product]

I am attempting to project the related category collection (m:n) in the simplest way possible and not have the category projection code in several different places. I have attempted two different ways to do this, as detailed in the repository class below:

  public Func<CategoryEntity,Category> CategoryProjector
        {
            get
            {
                return (c => new Category
                              {
                                  Id = c.Id,
                                  UrlId = c.UrlId,
                                  Name = c.Name,
                                  Description = c.Description
                              });
            }
        }

  public IQueryable<Product> GetProducts()
        {
            var metaData = new LinqMetaData(DataAccessContext.Current);
            return from p in metaData.Product
                   select new Product
                              {
                                  Id = p.Id,
                                  UrlId = p.UrlId,
                                  Name = p.Name,
                                  Description = p.Description,
                                  Categories = p.CategoryCollectionViaProductCategoryMap.Select(CategoryProjector).ToList()
                              };
        }

        public IQueryable<Product> GetProductsWithCategorySubquery()
        {
            var metaData = new LinqMetaData(DataAccessContext.Current);
            return from p in metaData.Product
                   select new Product
                   {
                       Id = p.Id,
                       UrlId = p.UrlId,
                       Name = p.Name,
                       Description = p.Description,
                       Categories = GetCategoriesForProduct(p.Id).ToList()
                   };
        }


        public IQueryable<Category> GetCategories()
        {
            var metaData = new LinqMetaData(DataAccessContext.Current);
            return metaData.Category.Select(CategoryProjector).AsQueryable();
        }

        public IQueryable<Category> GetCategoriesForProduct(Guid prodId)
        {
            return from c in GetCategories()
                   join pc in GetProductCategoryMap() on c.Id equals pc.CategoryId
                   where pc.ProductId == prodId
                   select c;
        }

        
        public IQueryable<ProductCategoryMap> GetProductCategoryMap()
        {
            var metaData = new LinqMetaData(DataAccessContext.Current);
            return from pcm in metaData.ProductCategoryMap
                   select new ProductCategoryMap
                   {
                       ProductId = pcm.ProductId,
                       CategoryId = pcm.CategoryId,
                   };
        }

When I run the following unit test:

 [Test]
        public void TestCategoryProjectionWithCollection()
        {
            var q = from p in _repository.GetProducts()
                    select p;

            foreach(var prod in q)
            {
                Assert.Greater(prod.Categories.Count,0);
            }

        }

I receive this exception:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ProjectionTest.DAL.EntityClasses.ProductEntity]).Select(p => new Product() {Id = p.Id, UrlId = p.UrlId, Name = p.Name, Description = p.Description, Categories = p.CategoryCollectionViaProductCategoryMap.Select(value(ProjectionTest.Data.ProductRepository).CategoryProjector).ToList()}).Select(p => p)
TestCase 'ProjectionTest.UnitTests.ProductTestFixture.TestCategoryProjectionWithCollection'
failed: System.InvalidCastException : Unable to cast object of type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.InMemoryEvalCandidateExpression' to type 'System.Linq.Expressions.LambdaExpression'.

And when running a unit test to test the second method of projecting (with a subquery rather than the relation collections):

[Test]
        public void TestCategoryProjectionWithQuery()
        {
            var q = from p in _repository.GetProductsWithCategorySubquery()
                    select p;

            foreach (var prod in q)
            {
                Assert.Greater(prod.Categories.Count, 0);
            }

        }

I get this exception:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ProjectionTest.DAL.EntityClasses.ProductEntity]).Select(p => new Product() {Id = p.Id, UrlId = p.UrlId, Name = p.Name, Description = p.Description, Categories = value(ProjectionTest.Data.ProductRepository).GetCategoriesForProduct(p.Id).ToList()}).Select(p => p)
TestCase 'ProjectionTest.UnitTests.ProductTestFixture.TestCategoryProjectionWithQuery'
failed: System.ArgumentException : An item with the same key has already been added.

I realize some of the code is inconsistent but as I mentioned it was just some quick test code I threw together. My apologies if I am missing something very obvious here. I basically would just like to have some method for using the projection code in various different contexts (1:n, m:n).

Please let me know if you would like me to post a complete VS solution.

Thanks.

Kevin

Could you also run this code with the tempbuild I mentioned in the other thread? Thanks.

I have run the tests with the same temp build and am seeing the same exceptions.

I will try to repro it, but NEXT TIME post way more info. It's already very painful to wade through all the linq crap MS has released, let alone puzzle for hours what the code looks like on your side and for example what the stacktrace is. (why didn't you post that? an exception without a stacktrace is useless)

If it takes too much time, I'll give up on this and wait till you've posted a reprocase.

My apologies on not posting the stack traces. Just an oversight on my part.

I figured I'd post some of the sample code before attaching a full solution and see how far we got.

Solution is attached along with a backup of the database. This includes the sorting issue from the other thread I started. Let me know if I can provide any additional information.

I fixed the sorting.

The issue of this thread is now on the plate. Stay tuned.

Great news. Thanks!

And actually, the sort issue can only be reproduced if you comment out the Categories projection in the ProductRepository class (i.e. the following line in ProductRepository.cs):

Categories = p.CategoryCollectionViaProductCategoryMap.Select(CategoryProjector).ToList()

But it sounds like you already have that covered :-)

The backup of the database can't be restored (log is missing) and I don't feel very happy about fighting with sqlserver 2005's management studio to get it restored somehow. I'll rebuild the tests for adventure works.

I reproduced the sorting with northwind order-customer.

Well... I have no idea how this could ever work with code running on the DB. I looked at the other thread more closely now, but there's one core problem: the method/property called which will produce the projection lambda returns a Func<>, not an expression<func<>>.

This means that when I call the method before the tree evaluation (so the result is pulled into the tree), I get a lovely delegate, but no projection.

When an Expression<Func<>> is returned, the code of course doesn't compile as Select wants a Func, not an Expression<Func<>>

When the method is totally isolated so doesn't use any element from the rest of the tree, it's compiled into code and ran. This is the current case. If I pass an element from the query so the method is tied to the rest and not isolated, I get a normal methodcall there, which is also not handleable, as I need the stuff INSIDE before the expression tree is evaluated.

I.o.w.: I'm without a clue why it works in the other thread. I'll post in that other thread a link to this thread, but without further in-depth info how this should ever work, I don't know how to proceed.

(edit) http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=79205&ThreadID=14157

there's also my testcode. Closed till further news pops up. Thread re-opens when a new post is posted.

Bryan mailed me a large project to check it out, waiting for the DB file to proceed. Though looking into his code shows that the projector call is at the outside of the method, not at the inside.

So I tried: var q = metaData.Product.Select(CreateProductProjector()).AsQueryable();

Which works.

So I thought... how?

Then I checked the expression tree passed to the linq provider. This is solely the metaData.Product element

So the .Select(... ) is done in memory.

The C# compiler does this, all I get passed in is a ConstantExpression, of type DataSource2<ProductEntity>, nothing else.

This makes perfect sense: the code inside the projector method is code which isn't compiled into an expression tree but it's a raw delegate and can never be used to be used on the DB.

So basically this line:

Categories = p.CategoryCollectionViaProductCategoryMap.Select(CategoryProjector).ToList()

would build the expression such that it is trying to do the projection in the DB? Which obviously isn't going to work?

Agreed, the problem is confirmed.

Maybe a different approach to the whole problem.

I am up for suggestions here.

Bryan

An approach which produces Expression<Func<>> instances for example like the PredicateBuilder class I posted here: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=78965&ThreadID=14144

Using extension methods to produce Expression objects which are then used inside the tree and will be part of the query, everything else, like the normal Func<T> stuff can't be translated and will be run in memory which should be avoided.

I had experimented a bit doing the projections using Expression<Func<>>and I think (not 100% sure) that was when I ran into some of the stack overflow exceptions. I will try to revisit this approach with a more recent build.

The very latest build has been uploaded 5 minutes ago. This one doesn't run into the stackoverflow probs you had previously.

I think the reason this all happens is that an IQueryable is also an IEnumerable. So it has 4 overloads of Select(): 2 from IQueryable (which accept an Expression<Func<>>) and 2 from IEnumerable.

Passing a Func<> to Select, means you pick the IEnumerable one, which automatically means the compiler won't place the Select() into the expression tree.

Looking at how this can be converted into an expression tree construct.

If I have: var q = metaData.Product.Select(p => new Product() { Name = p.ProductName, ProdId = p.ProductId }).AsQueryable();

q = q.Where(p => p.Name.Contains("a"));

(the p=> ... lambda is copied from the method called earlier) The 2nd argument of the Select method call is a lambda as you can see in the attached screenshot. (first the where call, then the select call, first argument is the source to select on, second argument is the lambda). So the expression to produce is the one in the lambda body, namely the MemberInit one.

First, let's look at what reflector tells us what the C# compiler builds from this

ParameterExpression CS$0$0000;
// ...
IQueryable<Product> q = metaData.Product.Select<ProductEntity, Product>(Expression.Lambda<Func<ProductEntity, Product>>(Expression.MemberInit(Expression.New((ConstructorInfo) methodof(Product..ctor), new Expression[0]), new MemberBinding[] { Expression.Bind((MethodInfo) methodof(Product.set_Name), Expression.Property(CS$0$0000 = Expression.Parameter(typeof(ProductEntity), "p"), (MethodInfo) methodof(ProductEntity.get_ProductName))), Expression.Bind((MethodInfo) methodof(Product.set_ProdId), Expression.Property(CS$0$0000, (MethodInfo) methodof(ProductEntity.get_ProductId))) }), new ParameterExpression[] { CS$0$0000 })).AsQueryable<Product>().Where<Product>(Expression.Lambda<Func<Product, bool>>(Expression.Call(Expression.Property(CS$0$0000 = Expression.Parameter(typeof(Product), "p"), (MethodInfo) methodof(Product.get_Name)), (MethodInfo) methodof(string.Contains), new Expression[] { Expression.Constant("a", typeof(string)) }), new ParameterExpression[] { CS$0$0000 }));

That's a lot of goo, so let's just focus on the important part:

IQueryable<Product> q = metaData.Product.Select<ProductEntity, Product>(
    Expression.Lambda<Func<ProductEntity, Product>>(
        Expression.MemberInit(
                Expression.New((ConstructorInfo) methodof(Product..ctor), new Expression[0]), 
                new MemberBinding[] 
                { 
                    Expression.Bind((MethodInfo) methodof(Product.set_Name), 
                                    Expression.Property(CS$0$0000 = 
                                            Expression.Parameter(typeof(ProductEntity), "p"), 
                                            (MethodInfo) methodof(ProductEntity.get_ProductName))), 
                    Expression.Bind((MethodInfo) methodof(Product.set_ProdId), 
                                    Expression.Property(CS$0$0000, 
                                            (MethodInfo) methodof(ProductEntity.get_ProductId))) 
                }), 
        new ParameterExpression[] { CS$0$0000 }))

where CS$0$0000 is a parameter expression. So this expression should be created through code and reflection. 'methodof' isn't proper C#, so we've to revert to a reflection call for that.

But...

Life is already as complex as it is... So I ran into this

and looked at how he implemented it. then I thought... but, I can leverage that as well.

So I did:

private static System.Linq.Expressions.Expression<Func<ProductEntity, Product>> CreateProjector()
{
    return p => new Product()
        {
            Name = p.ProductName,
            ProdId = p.ProductId
        };
}

and the query:

var q = metaData.Product.Select(CreateProjector()).AsQueryable();
q = q.Where(p => p.Name.Contains("a"));

where the only difference is... System.Linq.Expressions.Expression<Func<ProductEntity, Product>>

instead of Func<ProductEntity, Product>

This query above gave the same expression tree (i.e: with the select and where all nicely together into the same expression tree)

Now, if you have this:

var q = from o in metaData.Order
        select new Order
        {
            OrdId = o.OrderId,
            RelatedCustomer = new Customer
            {
                CompanyNme = o.Customer.CompanyName,
                CustId = o.Customer.CustomerId
            },
            Products = o.ProductCollectionViaOrderDetail.Select(CreateProductProjector()).ToList()   // compile error
        };

You'll get a compile error. This is because o.ProductCollectionVia... is an IEnumerable. Looking at how to make that work as well...

(edit) the m:n nested query isn't really doable. the problem is that the parent set is a set of orders and the nested set is a set of products. They don't have a direct relation so the merger can't merge them together, as the merge is done over field-field comparisons (using hashes). This is a known limitation for the nested set routines. We will look into making this more flexible in future versions.

So I dont have an answer for the nested set stuff using this projection route...