Issue with Join not working

Hi Guys, thanks for a great product. I am currently trying to do a few queries with the join and have noticed some unusually behaviour.

For example here is some code using Linq2Sql which works fine:

Snippet

            var dbClasses = new DataClasses1DataContext();
 
            var customers1 = from c in dbClasses.ondp_Customers
                             where c.SapBusinessUnitId == 2
                             select c;
 
            var customers2 = from c in customers1
                             where c.IsExternal == false
                             select c;
 
            var projects1 = from p in dbClasses.ondp_Projects
                            where p.DateEntered < DateTime.Now
                            select p;
 
            var projects2 = from p in projects1
                            where p.DateEntered > DateTime.Now.AddDays(-160)
                            select p;
 
            var requests = from request in dbClasses.ondp_Requests
                           join project in projects2 on request.ProjectId equals project.ReferenceId
                           join customer in customers2 on project.CustomerId equals customer.CustomerId
                           select request;
 
            var count1 = requests.ToList().Count;

This fires off some SQL to the database which has two joins in the SQL with all the where clauses attached.

If I do the same thing with Lightspeed:

Snippet

            var unitOfWork = new LightSpeedModelUnitOfWork();
            unitOfWork.Context = new LightSpeedContext("ONDPModelUnitOfWork");
 
            var lsCustomers1 = from c in unitOfWork.OndpCustomers
                                where c.SapBusinessUnitId == 2
                                select c;
 
            var lsCustomers2 = from c in lsCustomers1
                                where c.IsExternal == false
                                select c;
 
            var lsProjects1 = from p in unitOfWork.OndpProjects
                                where p.DateEntered < DateTime.Now
                                select p;
 
            var lsProjects2 = from p in lsProjects1
                                where p.DateEntered > DateTime.Now.AddDays(-160)
                                select p;
 
            var filteredRequests = from request in unitOfWork.OndpRequests
                                join project in lsProjects2 on request.ProjectId equals project.Id
                                join customer in lsCustomers2 on project.CustomerId equals customer.Id
                                select request;
 
            var count3 = filteredRequests.ToList().Count;

Then I get "Function evaluation timed out", and the count actually returns the full count of the OndpRequest table (no db call though). 

If I remove one of the joins, for example:

Snippet

            var filteredProjects = from project in lsProjects2
                             join customer in lsCustomers2 on project.CustomerId equals customer.Id
                             select project;
 
            var count2 = filteredProjects.ToList().Count;

Then SQL is sent to the database and the where clause for Projects is created but the where clause applied to Customers isn't applied.

Would you be able to let me know if I am doing something wrong, or whether this functionality isn't yet supported or will be supported later?

Thanks!!

Hi Frank,

Joining against a subquery (as you are doing above) is not supported by the LINQ provider yet, however this is planned for the near future. In the meantime you can express these types of joins by using the LightSpeed Querying API:

e.g.

var q1 = new Query() { EntityType = typeof(OndpCustomer) };
q1.QueryExpression = Entity.Attribute("SapBusinessUnitId") == 2 && Entity.Attribute("IsExternal") == false;
       
var q2 = new Query() { EntityType = typeof(OndpProject) };
q2.QueryExpression = Entity.Attribute("DateEntered") < DateTime.Now && Entity.Attribute("DateEntered") > DateTime.Now.AddDays(-160);
q2.Join = Join.Inner(typeof(OndpProject), q1, "CustomerId", "Id");
       
var query = new Query() { EntityType = typeof(OndpRequest) };
query.Join = Join.Inner(typeof(OndpRequest), q2, "ProjectId", "Id");

var results = unitOfWork.Find(query);

Alternatively if you are able to restructure your LINQ query into a single query you would be able to directly join against the IQueryable roots (unitOfWork.OndpProjects and unitOfWork.OndpCustomers) and just express the appropriate criteria as a single statement, but I suspect you may have a reason for keeping it in seperate parts in which case using the querying API will likely be a better fit.

Jeremy

Hi Frank,

We have now added support for this in to the nightly builds - let us know how you get on with this.

Jeremy

Hi Jeremy

Thanks for letting us know, my initial tests show it is working great!  Are there any limitations to the join or should I be ok to throw everything at it...

--Frank