3

I'm still learning Entity Framework and Linq-To-Entities, and I was wondering if a statement of this kind is possible:

using (var context = new MyEntities())
{
    return (                    
        from a in context.ModelSetA.Include("ModelB")
        join c in context.ModelSetC on a.Id equals c.Id
        join d in context.ModelSetD on a.Id equals d.Id
        select new MyModelA()
        {
            Id = a.Id,
            Name = a.Name,
            ModelB = new MyModelB() { Id = a.ModelB.Id, Name = a.ModelB..Name },
            ModelC = new MyModelC() { Id = c.Id, Name = c.Name },
            ModelD = new MyModelD() { Id = d.Id, Name = d.Name }
        }).FirstOrDefault();
}

I have to work with a pre-existing database structure, which is not very optimized, so I am unable to generate EF models without a lot of extra work. I thought it would be easy to simply create my own Models and map the data to them, but I keep getting the following error:

Unable to create a constant value of type 'MyNamespace.MyModelB'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

If I remove the mapping for ModelB, ModelC, and ModelD it runs correctly. Am I unable to create new nested classes with Linq-To-Entities? Or am I just writing this the wrong way?

Improve this question
1
  • Nested ViewModels work fine in projections. Somehow this exception doesn't fit to your code example. Is this with the SQL Server provider? Commented Sep 30, 2011 at 21:27

3 Answers 3

Reset to default
4

What you have will work fine with POCOs (e.g., view models). Here's an example. You just can't construct entities this way.

Also, join is generally inappropriate for a L2E query. Use the entity navigation properties instead.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

The code I posted does not work though - It gives me the error I posted above. It works if I remove the nested classes and only return ModelA, but not if I leave them in. Also, I would love to use Nav Properties for this but I am not allowed to touch the database structure, and the database is badly designed, so EF is giving me all kinds of trouble trying to join my custom tables with the pre-existing tables.
Also, the classes (MyModelA, MyModelB, etc) I am working with are basically POCO's with PropertyChange notifications, not Entities
2

I have created your model (how I understand it) with EF 4.1 in a console application:

If you want to test it, add reference to EntityFramework.dll and paste the following into Program.cs (EF 4.1 creates DB automatically if you have SQL Server Express installed):

using System.Linq;
using System.Data.Entity;

namespace EFNestedProjection
{
    // Entities
    public class ModelA
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ModelB ModelB { get; set; }
    }

    public class ModelB
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class ModelC
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class ModelD
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    // Context
    public class MyContext : DbContext
    {
        public DbSet<ModelA> ModelSetA { get; set; }
        public DbSet<ModelB> ModelSetB { get; set; }
        public DbSet<ModelC> ModelSetC { get; set; }
        public DbSet<ModelD> ModelSetD { get; set; }
    }

    // ViewModels for projections, not entities
    public class MyModelA
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public MyModelB ModelB { get; set; }
        public MyModelC ModelC { get; set; }
        public MyModelD ModelD { get; set; }
    }

    public class MyModelB
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class MyModelC
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class MyModelD
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            // Create some entities in DB
            using (var ctx = new MyContext())
            {
                var modelA = new ModelA { Name = "ModelA" };
                var modelB = new ModelB { Name = "ModelB" };
                var modelC = new ModelC { Name = "ModelC" };
                var modelD = new ModelD { Name = "ModelD" };

                modelA.ModelB = modelB;

                ctx.ModelSetA.Add(modelA);
                ctx.ModelSetB.Add(modelB);
                ctx.ModelSetC.Add(modelC);
                ctx.ModelSetD.Add(modelD);

                ctx.SaveChanges();
            }

            // Run query
            using (var ctx = new MyContext())
            {
                var result = (
                    from a in ctx.ModelSetA.Include("ModelB")
                    join c in ctx.ModelSetC on a.Id equals c.Id
                    join d in ctx.ModelSetD on a.Id equals d.Id
                    select new MyModelA()
                    {
                        Id = a.Id,
                        Name = a.Name,
                        ModelB = new MyModelB() {
                            Id = a.ModelB.Id, Name = a.ModelB.Name },
                        ModelC = new MyModelC() {
                            Id = c.Id, Name = c.Name },
                        ModelD = new MyModelD() {
                            Id = d.Id, Name = d.Name }
                    }).FirstOrDefault();
                // No exception here
            }
        }
    }
}

This works without problems. (I have also recreated the model from the database (which EF 4.1 had created) in EF 4.0: It works as well. Not surprising since EF 4.1 doesn't change anything in LINQ to Entities.)

Now the question is why you get an exception? My guess is that there is some important difference in your Models or ViewModels or your query compared to the simple model above which is not visible in your code example in the question.

But the general result is: Projections into nested (non-entity) classes work. (I'm using it in many situations, even with nested collections.) Answer to your question title is: Yes.

Improve this answer

Comments

0

What Craig posted does not seem to work for nested entities. Craig, if I am misunderstood what you posted, please correct me.

Here is the workaround I came up with that does work:

using (var context = new MyEntities())
{
    var x = (                    
        from a in context.ModelSetA.Include("ModelB")
        join c in context.ModelSetC on a.Id equals c.Id
        join d in context.ModelSetD on a.Id equals d.Id
        select new { a, b, c }).FirstOrDefault();

    if (x == null)
        return null;

    return new MyModelA()
        {
            Id = x.a.Id,
            Name = x.a.Name,
            ModelB = new MyModelB() { Id = x.a.ModelB.Id, Name = x.a.ModelB..Name },
            ModelC = new MyModelC() { Id = x.c.Id, Name = x.c.Name },
            ModelD = new MyModelD() { Id = x.d.Id, Name = x.d.Name }
        };
}

Since Entity Framework can't handle creating nested classes from within the query, I simply returned an anonymous object from my query containing the data I wanted, then mapped it to the Model

Improve this answer

2 Comments

I mean that you can't generally do new Entity { SomeField = in a L2E query at all. Nested or otherwise. You can, OTOH, do new POCO { SomeField = .
@CraigStuntz Yes, the models in my original question are POCOs, not Entities. I can do new POCO { SomeField = ... }, but I cannot do new POCO1 { SomeField = new POCO2 { ... } }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.