C#: Comparing two Linq2Sql entities not working?

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...

From stackoverflow

• 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...

  Svish : but isnt that what it is supposed to do? or have I missunderstood something?

  Think Before Coding : Actually the comparison will not happen in the C# code, but in the Sql command. It seems that linq to sql doesn't generate the right sql command from this lambda.

  Svish : hm, thats a bit scary? Is there a way I can make it generate the right sql? Is there something wrong with my dbml file? Or something in the database?

  Think Before Coding : The fact is that there are a lot of things that can be written in C# that cannot be translated in Sql by linq... I'm digging further to see if I can find the reason in this particular case

  Svish : thank you :) looking forward to an explanation if you find one, cause this just doesn't make sense to me :p

  Svish : So comparing a foreign key entity with another entity is a no-go then? If so I must say that is a bit annoying... but something I will have to live with I guess. Weird thing is that the code doesn't crash. If I gave it something linq2sql couldn't use, it should crash imho...

  Svish : Tried using that GetCommand funciton now, and here actually both versions give the exact same sql... could there be something with deffered loading?

  Think Before Coding : Do you use specific options on your dataContext ?

  Svish : I do now, cause if not, even using IdEmployee doesn't work for some reason. I really don't want to though. Setting DeferredLoadingEnabled to false doesnt seem to do any good either.

  Think Before Coding : Yes, I think the probleme is in the configuration of you entities and association in the dbml file...

  Svish : Hm, but those should be correct. Using http://www.huagati.com/dbmltools/ to sync it. And as far as I can see, all is like it should be...

• 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.

  Svish : No, they have to be same type, cause if not it would fail on compile-time.

  Leandro López : You are right, check the edited notes.

  Think Before Coding : But linq to sql ensure instance equality through an identity map... so that's not the problem.

• 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.

  Svish : No, that fails on compile-time, since employee is not of type SomeThing.

  Tomas Lycken : Well, I made the attempt since SomeThing seems to have the property IdEmployee. If you're not querying for employees but for other things linked to employees, I'd recommend a reference to the ID (which, anyway, should be a unique primary key...) as you did it in your second expression.

  Svish : .Equals does not work either.

  Tomas Lycken : Well, it won't, since as you said they're not the same type - they won't be equal. What kind of relationship do you have between the Employees table and the SomeThings table? One-to-Many, or Many-to-Many? Which direction? (I'd actually just compare the Id properties - it does the job, right?)

  Svish : yeah, it does the job. I just find it annoying :p It is a One-to-Many. One employee can have many things.

  Tomas Lycken : If you necessarily wants it comparing the employee, and not the employeeID, you have to load the employee objects (as Sessiz Saat proposed) when you get the things. I have provided a code example of how to do this in an edit to my post.

• 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;
  

  Tomas Lycken : You could also do this by using the .Include(string EntityName) syntax.