C#: Comparing two Linq2Sql entities not working?

Question

Could anyone explain to my why this is not working?

In the database we have Employees with an IdEmployee column. We also have some tables with foreign keys to that one.

Say we have something like this:

var employee = dataContext.Employees.First(<some predicate>);
var someEntity = dataContext.SomeEntities.First(<some predicate>);

Why does this not work:

var something = someEntity.SomeThings.First(t => t.Employee == employee);

while this does:

var something = someEntity.SomeThings.First(t => t.IdEmployee == employee.IdEmployee);

I don't get it... Isn't the first version supposed to work? The Employee entity is from the same datacontext...

Answer 1

Edit : the preceding explanation is wrong since 'it works on my machine' :

var contract = dataContext.Contracts.First();
var room = dataContext.Rooms.First(r => r.Contract == contract);

something must be broken in your association between your classes.

You can get the executed command by using

  var command = dataContext.GetCommand(
         dataContext.Rooms.Where(r => r.Contract == contract))
     .CommandText;

The resulting command text is :

SELECT ...    FROM [Rooms] AS [t0]
WHERE [t0].[ContractId] = @p0

So we can see that the primary key is found from the entity type...

---

Isn't the first version supposed to work? The Employee entity is from the same datacontext...

Yes but the lambda expression is converted to SQL script and it seems that the linq Sql script builder doesn't try to find the entity key to generate the request.

The linq to Sql command builder use the lambda content as an Expression<> tree to build the query text. To perform this operation it expect the lambda to contain expressions referencing entities properties.

The problem here seems to be that it references the entities themselve and not there properties.

The following query

var something = someEntity.SomeThings
    .First(t => t.IdEmployee == employee.IdEmployee);

produces Sql that will be something like

SELECT ... FROM SomThings
WHERE IdEmployee = @idEmployee

The table columns are found using the properties names in the lambda.

In the other case, there is no property name...

Answer 2

I think the first one does not work because the types are different, but in the second they are not (you're dealing with integers in the second one).

You should override the Equals (and GetHashCode) method in order to implement your own equality comparission.

---

Edit

As you have pointed out they have to be same type, cause if not it would fail on compile-time, but I would insist that it might be convenient to implement your own Equals and GetHashCode functions. I think your problem lies there. I can recall the link now, but I've read somewhere that it's a bit tricky the equality comparission in .NET.

Check this example:

using System;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Employee a = new Employee(1);
            Employee b = new Employee(1);

            Console.WriteLine("a == b := {0}", a == b);
            Console.ReadLine();
        }
    }

    class Employee
    {
        private int id;

        public Employee(int id) { this.id = id; }
    }
}

The output is:

a == b := False

That's because .NET internally uses GetHashCode and Equals methods from Object.

Answer 3

It is a little bit tricky to know for sure when you have used names like SomeThings and someEntity, but what I believe is happening is that when you're looking at someEntity.SomeThings, SomeThings is a collection of Employee entities. Thus, the t in your lambda expression will refer to an Employee object, giving you the ability to compare their properties.

Try the following

var something = someEntity.SomeThings.First(t => t.Equals(employee));

and see if that works better.

EDIT: Actually, the .Equals() syntax is better than the == I first proposed...

---

EDIT2: As Sessiz Saas proposes, it is probably because of lazy loading. An alternate way of loading the employee objects is provided below:

var myThings = dataContext.Things.Include("employee");

However, remember that this could cause extra (and unnecessary, as you are able to do the job anyway) data calls.

Answer 4

Because of lasy loading. someEntity.Employee property isn't loaded from DB. if you want to load it also, you should use "DataLoadOptions" before retriving data from DB,syntax is like:

DataLoadOptions options = new DataLoadOptions();
  options.LoadWith<SomeThing>(s=>SomeProperty);
  dataContext.LoadOptions = options;