locked
LINQ to Entities Query not working RRS feed

  • Question

  • I have a database with two tables (UserData) and (UserDetails). I use the LINQtoEntities.
    The issue here is that in the LINQ query is not accepted: "Details = c.ToList<UserDetails>()"

    Error is: 
    Instance argument: cannot convert from 'System.Collections.Generic.IEnumerable<mynamespace.UserDetails>' to 'System.Collections.Generic.IEnumerable<System.Collections.IEnumerable>'

    public class UserData
            {
    
                public int Index{ get; set; }
                public string Name{ get; set; }
                public string PersonalNo { get; set; }
                public <List<UserDetails>> Details { get; set; }
            }
    
    public struct UserDetails
            {
                public int Age;
                public string profession;
                public string gender;
            }
    
    
            public IEnumerable<Userdata> GetUserData()
            {
                var context = new DatabaseEntities();
    
                var Results =
                    from a in context.UserData
                    join b in context.UserDetails on a.Index equals b.Index into c
                    select new UserData{ Index = a.Index, Name = a.Name, PersonalNo = a.PersonalNo, 
                                         Details= c.ToList<UserDetails>() };
    
                        
    
                return Results;          
            }

    Is there anybody who can help me out. In case more infos needed, please ask.
    Thanks.

    Wednesday, February 4, 2015 10:11 PM

Answers

  • create the struct UserDetails in the query itself

    public class UserData
    {
     public int Index{ get; set; }
     public string Name{ get; set; }
     public string PersonalNo { get; set; }
     public IList<UserDetails> Details { get; set; }
    }

    public struct UserDetails
    {
     public int Age;
     public string profession;
     public string gender;
    }


    public IEnumerable<Userdata> GetUserData()
    {
     var context = new DatabaseEntities();

     var Results =
      from a in context.UserData
      join b in context.UserDetails on a.Index equals b.Index into c
      select new UserData{ Index = a.Index, Name = a.Name, PersonalNo = a.PersonalNo,
            Details= c.Select(f => new UserDetails { Age = f.Age, profession = f.profession, gender = f.gender }).ToList()};

       

     return Results;         
    }

    • Marked as answer by Fred Bao Monday, February 16, 2015 9:51 AM
    Monday, February 9, 2015 11:31 AM

All replies

  • Hello,

    >>The issue here is that in the LINQ query is not accepted: "Details = c.ToList<UserDetails>()"

    According to your provided code, I created a similar environment: two tables:

    public partial class Order
    
        {
    
            public Order()
    
            {
    
                this.OrderDetails = new List<OrderDetail>();
    
            }
    
        
    
            public int OrderID { get; set; }
    
            public string OrderName { get; set; }
    
        
    
            public virtual List<OrderDetail> OrderDetails { get; set; }
    
        }
    
    public partial class OrderDetail
    
        {
    
            public int OrderDetailID { get; set; }
    
            public string OrderDetailName { get; set; }
    
            public Nullable<int> OrderID { get; set; }
    
            public Nullable<decimal> Count { get; set; }
    
        
    
            public virtual Order Order { get; set; }
    
        }
    

    A DTO class for returned type:

    class OrderDTO
    
        {
    
            public int OrderID { get; set; }
    
            public List<OrderDetail> OrderDetails { get; set; }
    
        }
    

    Creating this type rather than using the Order directly because the entity or complex type cannot be constructed in a LINQ to Entities query.

    A similar query:

    class Program
    
        {
    
            static void Main(string[] args)
    
            {
    
                try
    
                {
    
                    using (DFDBEntities db = new DFDBEntities())
    
                    {
    
                        var result = GetUserData(db);
    
                    }
    
                }
    
                catch (Exception ex)
    
                {
    
                }
    
            }
    
    
            public static IEnumerable<Order> GetUserData(DFDBEntities db)
    
            {
    
                var result = (from o in db.Orders
    
                              join od in db.OrderDetails on o.OrderID equals od.OrderID into c
    
                              select new Order { OrderID = o.OrderID, OrderDetails = c.ToList<OrderDetail>() });
    
                return result;
    
            }
    
        }
    

    However, the result is returned as excepted, the tested is done with VS2013, SQL Server 2012 Express and .NET 4.5. Is it similar with yours? If not, please provide these information, and if is, please have a try with my demo.

    I notice that in your provided entity UserData, for the Details property, it is defined as “<List<UserDetails>>”, I am wondering if it throws a compiled exception because as far as I know, the extra “<>” is invalid in C# develop language.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 5, 2015 6:52 AM
  • Hi,

    I do have more or less the same ErrorMessage:

    Error 2 Instance argument: cannot convert from 'System.Collections.Generic.IEnumerable<LINQEntitiesApplikation.OrderDetails>' to 'System.Collections.Generic.IEnumerable<LINQEntitiesApplikation.QueryClass.OrderDetail>'

    Could you provide a complete listing?

    I am using VS2013 SQL Compact Database and Framework 4.5.
    Thanks.

    Thursday, February 5, 2015 1:53 PM
  • Just as an example. To get it working you can use object. See below the example:

    public class QueryClass
        {
            public class Order
            {
                public int OrderID { get; set; }
                public string OrderName { get; set; }
                public List<object> OrderDetails { get; set; }
            }
    
            public partial class OrderDetail
            {
                public int Id { get; set; }
                public int OrderDetailID { get; set; }
                public string OrderDetailName { get; set; }
            }
    
        
            public static IEnumerable<Order> GetUserData()
            {
                CompactDBEntities db = new CompactDBEntities();
    
                var result = (from o in db.Orders
                              join od in db.OrderDetails on o.OrderID equals od.OrderDetailsID into c
                              select new Order { OrderID = (int)o.OrderID, OrderDetails = c.ToList<object>() });
               
    
                return result;
            }
        }

    Then it is working. The Type in the Watch windows is names as "object {LINQEntitiesApplikation.OrderDetail}"
    So basically it is working as I did it. But I need to cast it appropriate. Possibly now it is easier to help me.
    Any suggestions?

    Thursday, February 5, 2015 8:56 PM
  • Hello,

    >> cannot convert from 'System.Collections.Generic.IEnumerable<LINQEntitiesApplikation.OrderDetails>' to 'System.Collections.Generic.IEnumerable<LINQEntitiesApplikation.QueryClass.OrderDetail>'

    For this exception, it shows that you are trying to convert 'System.Collections.Generic.IEnumerable<LINQEntitiesApplikation.OrderDetails>' to 'System.Collections.Generic.IEnumerable<LINQEntitiesApplikation.QueryClass.OrderDetail>', however, this is not allowed in .NET, please make sure the target type is still LINQEntitiesApplikation.OrderDetails the same with the original one.

    >>Could you provide a complete listing?

    I do not quite understand, in my previous post, I have already post all the code.

    >> Then it is working. The Type in the Watch windows is names as "object {LINQEntitiesApplikation.OrderDetail}"

    Doesn’t it work if you replace the object with OrderDetail? Or have a try to place these entities outside the QueryClass rather than make them be nested classes:

    public class Order
    
            {
    
                 public int OrderID { get; set; }
    
                 public string OrderName { get; set; }
    
                 public List< OrderDetail > OrderDetails { get; set; }
    
            }
    
            public partial class OrderDetail
    
            {
    
                 public int Id { get; set; }
    
                 public int OrderDetailID { get; set; }
    
                 public string OrderDetailName { get; set; }
    
            }
    
    public class QueryClass
    
        { 
    
            public static IEnumerable<Order> GetUserData()
    
            {
    
                 CompactDBEntities db = new CompactDBEntities();
    
                 var result = (from o in db.Orders
    
                              join od in db.OrderDetails on o.OrderID equals od.OrderDetailsID into c
    
                              select new Order { OrderID = (int)o.OrderID, OrderDetails = c.ToList<  OrderDetail >() });
    
               
    
                 return result;
    
            }
    
        }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred Bao Friday, February 6, 2015 6:13 AM
    Friday, February 6, 2015 6:11 AM
  • Hi, let me give you an example which I have shortened a bit with hopefully a complete listing incl. Database Schema. Please find below the listing: (You can switch between the "c.ToList<object>()" and "c.ToList<clsUserDetails>()"
    In case you take the Object one then it is working. But this is not what I want to have.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace LINQEntitiesApplikation
    {
    
        public class QueryClass
        {
            public class clsUserData
            {
                public int Id { get; set; }
                public string Name { get; set; }
                public string Vorname { get; set; }
                public List<object> Details { get; set; }
            }
    
            public class clsUserDetails
            {
                public int UserDataID;
                public int Alter;
                public string Augenfarbe;
                public string Personalnummer;
            }
    
            public IEnumerable<clsUserData> GetUserData()
            {
                var context = new CompactDBEntities();
    
                var Results =
                    from a in context.UserData
                    join b in context.UserDetails on a.Id equals b.UserDataID into c
                    select new clsUserData
                    {
                        Id = a.Id,
                        Name = a.Name,
                        Vorname = a.Vorname,
                        Details = c.ToList<object>()
                    };
    
                return Results;
            }
        }
    
        //public class QueryClass
        //{
        //    public class clsUserData
        //    {
        //        public int Id { get; set; }
        //        public string Name { get; set; }
        //        public string Vorname { get; set; }
        //        public List<clsUserDetails> Details { get; set; }
        //    }
    
        //    public class clsUserDetails
        //    {
        //        public int Alter;
        //        public string Augenfarbe;
        //        public string Personalnummer;
        //    }
    
        //    public IEnumerable<clsUserData> GetUserData()
        //    {
        //        var context = new CompactDBEntities();
    
        //        var Results =
        //            from a in context.UserData
        //            join b in context.UserDetails on a.Id equals b.UserDataID into c
        //            select new clsUserData
        //            {
        //                Id = a.Id,
        //                Name = a.Name,
        //                Vorname = a.Vorname,
        //                Details = c.ToList<clsUserDetails>()
        //            };
    
        //        return Results;
        //    }
        //}
    }

    Here the Database I use for this example (with this Schema you can easily build the test environment:

    CREATE TABLE [dbo].[UserData] (
        [Id]      INT           NOT NULL,
        [Name]    NVARCHAR (50) NULL,
        [Vorname] NVARCHAR (50) NULL
    );
    
    CREATE TABLE [dbo].[UserDetails] (
        [UserDataID]     INT        NOT NULL,
        [Alter]          INT        NULL,
        [Augenfarbe]     NCHAR (10) NULL,
        [Personalnummer] INT        NULL
    );

    The whole Process will be started with a small form where I put the following code:

    private void button1_Click(object sender, EventArgs e)
            {
                QueryClass myclass = new QueryClass();
                myclass.GetUserData();
            }

    Is there still anything unclear or you need more info, please let me know.
    Thanks.

    Friday, February 6, 2015 9:39 AM
  • Hello,

    Thanks for providing these detail information, I will have a try to reproduce this issue, it will take some time, as soon as I get any result, I will tell you.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 9, 2015 10:58 AM
  • create the struct UserDetails in the query itself

    public class UserData
    {
     public int Index{ get; set; }
     public string Name{ get; set; }
     public string PersonalNo { get; set; }
     public IList<UserDetails> Details { get; set; }
    }

    public struct UserDetails
    {
     public int Age;
     public string profession;
     public string gender;
    }


    public IEnumerable<Userdata> GetUserData()
    {
     var context = new DatabaseEntities();

     var Results =
      from a in context.UserData
      join b in context.UserDetails on a.Index equals b.Index into c
      select new UserData{ Index = a.Index, Name = a.Name, PersonalNo = a.PersonalNo,
            Details= c.Select(f => new UserDetails { Age = f.Age, profession = f.profession, gender = f.gender }).ToList()};

       

     return Results;         
    }

    • Marked as answer by Fred Bao Monday, February 16, 2015 9:51 AM
    Monday, February 9, 2015 11:31 AM
  • Hi Vineet,

    many thanks for you answer with your code listing. I had to make some minor change to get it finally working, because a "structure" inside a LINQ-query is not workign as it throws a runtime error:

    Message "Only parameterless constructors and initializers are supported in LINQ to Entities." string

    I changed the following code:

    public struct UserDetails
    {
     public int Age;
     public string profession;
     public string gender;
    }
    into this:
    public class UserDetails
    {
     public int Age { get; set; }
     public string profession { get; set; }
     public string gender { get; set; }
    }
    But I am still a bit worried that this is working, as we all know that a class instance
    just copies the reference and does not clone. This should have the effect that all entries have the
    same value at the end, rather having unique data per row. But somehow it's working. Possibly inside LINQ
    it behaves different. Can you confirm my doubts?
    Thanks.
    Monday, February 9, 2015 2:00 PM
  • Yes you are right about class instance just copies the reference and not close, but here in LINQ it creates new instances for every rows from the UserDetails that's why you are getting unique data per row.
    Monday, February 9, 2015 2:08 PM