Problems with 'eager load' mode.

I found a few problems when using the 'eager load' while loading data. To demonstrate them, I attach with the project file to the message. I use LightSpeed ​​version 5.0.2302.0. Microsoft SQL Server 2012. Microsoft Visual Studio 2012 ,. Net Framework 4.5.

I have a simple database with three tables: Company ---> Department ---> Employee. Both associations (one-to-many) with 'true' options "Eager Load Backreference" and "Eager Load Collection".

Problem #1. I want to get data from the database. I use the following code:

IEnumerable<Employee> employees = unitOfWork.Query<Employee>()
    .Take(7)
    .OrderByDescending(e => e.Department.Company.Title);

As a result, getting an exception :

"Invalid column name 'Company' (. Net SqlClient Data Provider)."

If you view the text of the query to the database , you can see that it is made with syntax errors (eg, 'Department.Company'):

... ROW_NUMBER () OVER (ORDER BY Department.Company DESC, Department1.Title DESC, Employee.Title DESC) as RowNumber

What to do in this situation?

Problem #2. I want to get data from the database. I use the following code:

IEnumerable<Employee> employees = unitOfWork.Query<Employee>();

This code works successfully. But if you view the text of the query to the database, you will see the following.

SELECT
  Employee.Id AS [Employee.Id],
  Employee.DepartmentId AS [Employee.DepartmentId],
  Employee.Title AS [Employee.Title]
FROM
  Employee;

SELECT
  Department.Id,
  Department.CompanyId,
  Department.Title
FROM
  Department;

SELECT
  Company.Id,
  Company.Title
FROM
  Company

Why the second and third query selects all data from tables 'Department' and 'Company', and not only those which are necessary for the first query data? What if the table extensive (eg, a few thousand or a million rows)?

Sincerely, Maxim.

Mindscape }}}... Is anybody home?

Thanks for the repro for #1 - this looks like an issue with applying the ordering to the SQL row_number paging so we are looking into a fix for this. Ill update the thread once we have made some progress on this.

In terms of #2 this is expected behaviour. Eager loads are filtered by the same criteria applied to the query since they are executed in a single batch.

Jeremy

Thanks for the answer, Jeremy.

I'm surprised at the answer on the problem #2.

"Expected Behavior"? It's incredible.

Consider a simple example. Data model - insurance contracts and insurance events. Every insurance contract may have several insurance events, but most contracts do not have insurance events. If I want to get a list of all insurance events, why with such a request will be received all insurance contracts? Of course, I can add a fictitious condition in the query (eg, "contract.Id > 0"), then the request will be made correctly. But is this normal behavior?

Yes, if you are using eager loading then you are asking LightSpeed to load all related entities across that association in the same query batch. We dont know therefore what entities will be in that batch. If you are doing this on a single entity we can then scope the query for children by the Id of the parent. If there is a range of entities filtered by some common criteria (e.g. where contract.Id > 0) then we can pass this down as criteria in the child query. If there is no criteria (e.g. loading whole table) then no criteria is used when loading the children, so all possible children get loaded.

Stepping back from this, you are obviously going to have a much better understanding of the model than LightSpeed so if what is occurring seems wrong than maybe a lazy loaded strategy would be more appropriate. Similarly if you know it needs to be eager loaded but most of the children are not associated, putting in some criteria so that it is passed down in the query may be more sensible since it helps filter the eager load queries according to your needs.

Jeremy

Thanks for the answer, Jeremy.

But I do not agree with you. LightSpeed does not require information on the subject area, as you say. For example. Adding a absolutely fictitious conditions leads to the formation of the correct query. Condition:

unitOfWork.Query<Employee>().Where(p => true)

Queries:

SELECT
  Employee.Id AS [Employee.Id],
  Employee.DepartmentId AS [Employee.DepartmentId],
  Employee.Title AS [Employee.Title]
FROM
  Employee
WHERE
  Employee.Id = Employee.Id;

SELECT
  Department.Id,
  Department.CompanyId,
  Department.Title
FROM
  Department
WHERE
  EXISTS (
    SELECT
      Employee.*
    FROM
      Employee
    WHERE
      Employee.DepartmentId = Department.Id AND
      Employee.Id = Employee.Id
  );

SELECT
  Company.Id,
  Company.Title
FROM
  Company
WHERE
  EXISTS (
    SELECT
      Department.*
    FROM
      Department
    WHERE
      Department.CompanyId = Company.Id AND
      EXISTS (
        SELECT
          Employee.*
        FROM
          Employee
        WHERE
          Employee.DepartmentId = Department.Id AND
          Employee.Id = Employee.Id
      )
  )

Why you can not generate the second and third queries correctly (with keyword 'EXISTS'), even if the fictitious condition is not added?

Next. In this example, the data retrieval is performed by three requests. But this may result in inconsistent data (for example, while executing a second query may change the data set for the first query). Why you can not get all the data in a single query, (with keyword 'LEFT JOIN')?

It certainly is possible, and as you have noted you can trigger this behaviour yourself by specifying criteria that would match every row, however this is not currently (or historically) how we approach eager loads in terms of applying criteria through our mapping pipeline. What you are suggesting is certainly reasonable and Ive made a note on our backlog to review this.

In terms of the consistency, thats right, we dont attempt to cover this if this is of particular concern you would want to look at wrapping data access in a transaction at an appropriate scope and isolation level to manage those concerns.

Jeremy

Thanks for your answers, Jeremy.