none
Select new {} in a many to many relation query : IEnumerable<AnonymousType> to EntityCollection<ChildType> issue RRS feed

  • Question

  • Hello

     

    i try to get just the data i need using anonymous type in a linq query (i.e. the same thing that an include but with selected data on all levels).

    i found this to retrieve selected data from one table

    http://social.msdn.microsoft.com/Forums/en-IE/adodotnetentityframework/thread/94a156cb-563b-4369-93a0-5169b7daec61

    I write a query to retrieve data from child table:


                    var Query =
                         from c in context.Level1
                         select new
                            {
                                Field1Level1 = c.Field1Level1,
                                 Level2 = from p in context.Level2 select new
                                                {
                                                Field1Level2 = c.Field1Level2
                                                }
                     };


    i then try to cast each anonymous type 

                   List<Level1> testlist = new List<Level1>();

                    foreach (var q in 4Query)
                    {
                        testlist.Add(new Level1() { Field1Level1 = q.Field1Level1, Level2 = q.Level2 });
                    }

    where Level2 = q.Level2 give an error : cannot implicitly convert IEnumerable<AnonymousType> to EntityCollection<ChildType>

    The simplest solution would be something like that :

                    List<Level1> tree =
                        (from cWithP in
                            (from c in context.Level1

                                select new Level1()
                                {
                                     Field1Level1 = c.Field1Level1,
                                     Level2 = from p in c.Level2
                                                  select new Level2()

                                                       { Field1Level2 = p.Field1Level2}

                              }).AsEnumerable()
                         select cWithP.Level1).ToList();

    but it give me the same error. any suggestions ?

    • Edited by Pansoul Thursday, June 9, 2011 8:30 AM mistake due to a mix between my real code and my example code
    Wednesday, June 8, 2011 8:34 PM

Answers

  • Hi,

    Why do you want to use an anonyous type?

    If you have an Many-To-Many relation, your Level1 table should hold a navigationProperty to the related entities of Level2 table?

    You should only write something like this:

    context.Level1.Include("<the navigation property to Level2 entities>")

    and you will get your Level1 entities with the Navigation property Level2 filled with data.

    But the reason you'll get your error is that q.Level2 isn't of the same type as the Level2 property.

    You should instead try:

    Level2 = new Level2() { Field1Level2 = q.Level2.Field1Level2 }

     

    Hope this helps!


    --Rune
    Thursday, June 9, 2011 6:14 AM
  • Hi,

    No, that's correct, you cannot do that in "all-in-one", you need to do it in two steps unfortunatly. First retrieve a list with your anonymous fields, and then generate your entities based on the result of your first query.

     

     

     


    --Rune
    Thursday, June 9, 2011 10:21 AM

All replies

  • Hi,

    Why do you want to use an anonyous type?

    If you have an Many-To-Many relation, your Level1 table should hold a navigationProperty to the related entities of Level2 table?

    You should only write something like this:

    context.Level1.Include("<the navigation property to Level2 entities>")

    and you will get your Level1 entities with the Navigation property Level2 filled with data.

    But the reason you'll get your error is that q.Level2 isn't of the same type as the Level2 property.

    You should instead try:

    Level2 = new Level2() { Field1Level2 = q.Level2.Field1Level2 }

     

    Hope this helps!


    --Rune
    Thursday, June 9, 2011 6:14 AM
  • I use anonymous type because i didn't find another way to retrieve only certain fields.

    Let's say that the level2 contains Field1Level2, Field2Level2, Field3Level2. I'm interested in getting only Field1.

    The problem with Include is that it retrieves the 3 fields.

    In the link in my first post, there is an example of using anonymous type to retrieve only the needed data.

    after the query it uses a foreach loop to recreate the entity type but this example work for a query on one table, not a tree structure

    About Level2 = new Level2() { Field1Level2 = q.Level2.Field1Level2 } i'm sorry it was a mistake due to a difference between my real code and my example code

    but the correct code give the same error.

    i tried another syntax

    List<Level1> tree =
                        (from cWithP in
                            (from c in context.Level1

                                select new Level1()
                                {
                                     Level1 = new Level1() { Field1Level1 = c.Field1Level1 },
                                     Level2 = from p in c.Level2
                                                  select new Level2()

                                                       { Field1Level2 = p.Field1Level2}

                              }).AsEnumerable()
                         select cWithP.Level1).ToList();

    where i change Field1Level1 by Level1 = new Level1() { Field1Level1 = c.Field1Level1 }

    it compiles this time but i have an exception

    The entity or complex type 'urgencesModel.CCAM_ChapNiv1' cannot be constructed in a LINQ to Entities query



    Thursday, June 9, 2011 9:05 AM
  • Hi,

    No, that's correct, you cannot do that in "all-in-one", you need to do it in two steps unfortunatly. First retrieve a list with your anonymous fields, and then generate your entities based on the result of your first query.

     

     

     


    --Rune
    Thursday, June 9, 2011 10:21 AM