2

I am passing values into a method which uses a foreach loop to iterate through a collection. In the loop, an Include statement is used from entity framework to eager load. This is what I pass in:

var exp = new Collection<Expression<Func<Foo,object>>>();

Why is it that when I use this:

exp.Add(f => f.Bars.Select(b=> b.Employees.Select( e=> e.Position)));
exp.Add(f => f.Bars.Select(b=> b.Employees.Select( e=> e.Bank)));

and Employee, Position, and Bank all have the field Name that it will jumble the Name between the different fields? As in, Bank and Position will both have Employee's name in their Name field. To be more explicit, for whatever reason

In DataBase:

Employee.Name = "Jon";
Employee.Bank.Name = "World Bank";
Employee.Position.Name = "CEO";

Data from .Include:

Employee.Bank.Name == "Jon" //true
Employee.Position.Name == "Jon" //true

Extra Information, inside of method that accepts exp

DbSet<Foo> dbSet = context.Set<Foo>();
IQueryable<Foo> query = dbSet;

if (exp != null)
{
 foreach (var incProp in exp)
 {
  query = query.Include(incProp);
 }
}

Am I doing something wrong in my code?

edit

public class Foo
{
 public int FooId { get; set; }
 public virtual List<Bar> Bars { get; set; }
}

public class Bar
{
 public int BarId { get; set; }
 public virtual Foo Foo { get; set; }
 public int FooId { get; set; }
 public virtual List<Employee> Employees { get; set; }
}

public class Employee
{
 public int EmployeeId { get; set; }
 public int BarId { get; set; }
 public virtual Bar Bar { get; set; }
 public int BankId { get; set; }
 public virtual Bank Bank { get; set; }
 public int PositionId { get; set; }
 public virtual Position Position { get; set; }
 public string Name { get; set; }
}

public class Bank
{
 public int BankId { get; set; }
 public string Name { get; set; }
}

public class Position
{
 public int PositionId { get; set; }
 public string Name { get; set; }
}
Slauma
  • 175,098
  • 59
  • 401
  • 420
Travis J
  • 81,153
  • 41
  • 202
  • 273
  • How are `f`, `b` and `e` declared? Could you have a mismatch in the entity framework between data in SQL and objects in your code? – HeatfanJohn Oct 10 '12 at 18:16
  • @HeatfanJohn - See edit for an example of how they are declared – Travis J Oct 10 '12 at 18:23
  • @HeatfanJohn - "mismatch in the ef between data in sql and objects in your code", can you explain what you mean a little more here? There is clearly something mapping wrong, and it seems to me to be the result of a malformed SQL statement. I am not sure if my code is causing the statement to be malformed. – Travis J Oct 10 '12 at 18:24
  • FWIW @TravisJ confirmed this is not a matter of closing over the `foreach` loop variable. – Yuck Oct 10 '12 at 18:36
  • @Yuck - That is still a valid point though, I will try to make sure that is not a possibility perhaps in where the expressions are being added to exp up front. – Travis J Oct 10 '12 at 18:38
  • Did you try include without the expressions (`query.Include("Employees.Bank").Include("Employees.Bank")`)? Does it work as expected? – jeroenh Oct 10 '12 at 18:42
  • @jeroenh - I have indeed tried to use the "IncludeProperties" approach of just using strings. Unfortunately, this also results in the "jumbled" values being returned. – Travis J Oct 10 '12 at 18:44
  • `mysql` tag added. I believe it's important. – Slauma Oct 10 '12 at 19:36
  • @Slauma - Ok, thank you. I am not sure what else to do at this point. I was hoping that putting the graph together sub graph by sub graph would work but this is going to add a large amount of trips in my current situation. How would entity framework act differently with `mysql`? – Travis J Oct 10 '12 at 19:39
  • @TravisJ: The provider you are using is responsible for translating LINQ into the DB specific SQL dialect. `MS-SqlClient` -> translation into T-SQL for SQL Server, `MySqlClient` -> translation into the SQL that the MySQL DB understands. The "provider" is a component that is independent of EF and can be third-party (not made by MS), and your "provider" seems to have a bug in that it creates incorrect SQL for *some* complex LINQ queries. – Slauma Oct 10 '12 at 19:43
  • @Slauma - Do you think it is possible that the bug has already been fixed by a newer version than what I am using? This is one of the bug fixes they list in the newer version: `The MySQL script generated by using the function CreateDatabaseScript used names with incorrect singular/plural forms. (Bug #13582837, Bug #62150)` – Travis J Oct 10 '12 at 19:58
  • @TravisJ: I think `CreateDatabaseScript` is for creating or migrating a database schema rather than generating SQL from LINQ. I would suggest to test with the newest version (if you can easily upgrade) and if the problem remains, file a bug report (maybe refering to your two questions here). – Slauma Oct 10 '12 at 20:03
  • @Slauma - I successfully updated to the most current version (6.5.4) and am still encountering this issue. I will try to post a bug report at their site. – Travis J Oct 10 '12 at 20:30
  • @Slauma - You can see the bug report here: http://bugs.mysql.com/bug.php?id=67183 . Note that I included some of your code from the other question as an example of how to repeat the situation, I hope you don't mind. – Travis J Oct 10 '12 at 21:03
  • @Slauma - I came across this today: "Nested queries in a projection clause might get translated into Cartesian product queries on the server." -http://msdn.microsoft.com/en-us/library/bb896273.aspx What do you think? – Travis J Oct 11 '12 at 17:48
  • @TravisJ: The article is only talking about possible poor performance due to suboptimal translation into SQL in some situations, but not about wrong SQL with wrong results. Also the SQL generated (the one in your former question) isn't a cartesian product, it has the INNER and LEFT OUTER JOINs that is expected for eager loading. I think the article doesn't apply to your problem. – Slauma Oct 11 '12 at 20:38
  • @Slauma - I think you are correct, because upon inspecting mysqlconnector's MySql.Data.Entity.SqlGenerator.cs class I came across the problem. They use `String.Format("@gp{0}", parameterCount++);` in order to make unique `AS PARAM1234` descriptors and I believe that the class which holds `parameterCount` is somehow resetting the parameterCount to 1 in the nested projection causing a repetition of variable names and therefore a duplication of data with similar names. – Travis J Oct 11 '12 at 20:44

1 Answers1

3

I don't think the problem is in the code you're showing. I created a console app from what you've put above and it output what was in the database. Here's the entirety of the app:

namespace ExampleCF
{
    public class Foo
    {
        public int FooId { get; set; }
        public virtual List<Bar> Bars { get; set; }
    }

    public class Bar
    {
        public int BarId { get; set; }
        public virtual Foo Foo { get; set; }
        public int FooId { get; set; }
        public virtual List<Employee> Employees { get; set; }
    }

    public class Employee
    {
        public int EmployeeId { get; set; }
        public int BarId { get; set; }
        public virtual Bar Bar { get; set; }
        public int BankId { get; set; }
        public virtual Bank Bank { get; set; }
        public int PositionId { get; set; }
        public virtual Position Position { get; set; }
        public string Name { get; set; }
    }

    public class Bank
    {
        public int BankId { get; set; }
        public string Name { get; set; }
    }

    public class Position
    {
        public int PositionId { get; set; }
        public string Name { get; set; }
    }

    public class Model : DbContext
    {
        public DbSet<Foo> Foos { get; set; }
        public DbSet<Bar> Bars { get; set; }
        public DbSet<Employee> Employees { get; set; }
        public DbSet<Bank> Banks { get; set; }
        public DbSet<Position> Positions { get; set; }

        public Model()
        {
            Configuration.LazyLoadingEnabled = false;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Model context = new Model();
            var exp = new Collection<Expression<Func<Foo, object>>>();

            Foo foo = new Foo();
            Bar bar = new Bar();
            Employee emp = new Employee() { Name = "employee" };
            Bank bank = new Bank() { Name = "bank" };
            Position position = new Position() { Name = "position" };
            foo.Bars = new List<Bar>();
            foo.Bars.Add(bar);
            bar.Employees = new List<Employee>();
            bar.Employees.Add(emp);
            emp.Position = position;
            emp.Bank = bank;
            context.Foos.Add(foo);
            context.SaveChanges();

            exp.Add(f => f.Bars.Select(b => b.Employees.Select(e => e.Position)));
            exp.Add(f => f.Bars.Select(b => b.Employees.Select(e => e.Bank)));

            DbSet<Foo> dbSet = context.Set<Foo>();
            IQueryable<Foo> query = dbSet;

            if (exp != null)
            {
                foreach (var incProp in exp)
                {
                    query = query.Include(incProp);
                }
            }

            var first = query.ToList().FirstOrDefault();
            var firstEmp = first.Bars.First().Employees.First();
            Console.WriteLine(String.Format("{0} | {1} | {2}", firstEmp.Name, firstEmp.Bank.Name, firstEmp.Position.Name));
        }
    }

}

outputs: employee | bank |position

Is there anything else you're adding to the query, or maybe you're somehow creating an anonymous type?

Mark Oreta
  • 10,346
  • 1
  • 33
  • 36
  • I am not creating any anonymous types, or at least not that I am aware of. I do filter once sometimes, before the `if(exp!=null)` call, `query = query.Where(foo => foo.id == 1);` but that is all that gets added. – Travis J Oct 10 '12 at 19:02
  • I am at such a loss here. To note, the database is a mysql database using ` ` – Travis J Oct 10 '12 at 19:03
  • Do projections count as anonymous types? – Travis J Oct 10 '12 at 19:14
  • @TravisJ: **MySql?** Does it also apply to your old question here: http://stackoverflow.com/questions/11676513/how-can-i-use-entity-framework-on-an-object-graph-past-a-depth-of-2 ? "*...jumble the name between different fields...*" sounds like the same problem as in your old question and like Mark in his answer above I couldn't reproduce the problem either with SQL Server. Very suspicious... – Slauma Oct 10 '12 at 19:26
  • @Slauma - Yes, the same situation. I had a workaround that I was using which worked up until the point which I needed to use a more complex scenario and the workaround became even less desirable. :( – Travis J Oct 10 '12 at 19:29
  • @TravisJ: Then it looks like a bug in the `MySql` provider. Most likely it translates the LINQ query incorrectly into SQL which is the source for both the problem in your old question and in this one. Maybe file a bug report. – Slauma Oct 10 '12 at 19:39