Equivalent LINQ join to Query expression

I have browsed through a lot of join related posts but still cannot figure out this one:

I have the following LINQ expression that returns the correct results:

var  customers= unitOfWork.Customers
                        .Where(l => l.IsActive)
                        .Join(unitOfWork.Subscribers.Where(rs => rs.RuleId == 1),
                        l => l.Id,
                        r => r.CustomerId, (l, r) => l).ToArray();

I want to figure out the equivalent Query expression so that this is performed on the server:

I tried this:

 var join = Join.Inner <Customer, Subscriber>("Id", Subscriber.CustomerIdField); 
                    var query = new Query(typeof(Customer),
                        Entity.Attribute(Customer.IsActiveField) == true)
                    {                                                    
                        Join = join
                    };
                     //how do I filter Subscribers based on RuleId == 1?
                    customers= unitOfWork.Find<Customers>(query);

But it does not filter based on RuleId.

TIA.

Because you are joining against a query you need to actually express the sub-query using its own Query instance, you can then join against that with a different overload for Join.Inner.

e.g.

var query = new Query(typeof(Customer)); var queryInner = new Query(typeof(Subscriber));

query.QueryExpression = Entity.Attribute("IsActive") == true; queryInner.QueryExpression = Entity.Attribute("RuleId") == 1;

query.Join = Join.Inner(typeof(Customer), queryInner, "Id", "CustomerId");

var results = UnitOfWork.Find(query);

Jeremy

Great, thank you.

I followed this example but the SQL that is generated does not execute successfully.

I want all sessionusers where their session.startdatetime > current datetime. The FK relationship is sessionuser.sessionid = session.id

Query queryInner = new Query(typeof(Session)); queryInner.QueryExpression = (Entity.Attribute("StartDateTime") >= DateTime.Now);

Query query = new Query(typeof(SessionUser)); query.Join = Join.Inner(typeof(SessionUser), queryInner, "SessionId", "Id");

unitOfWork.Find(query) ...

This generates SQL like the following:

exec sp_executesql N'SELECT t0.Id AS "t0.Id", t0.SessionId AS "t0.SessionId" FROM SessionUser t0 INNER JOIN (SELECT Session.Id AS "Session.Id", Session.StartDateTime AS "Session.StartDateTime" FROM Session WHERE Session.StartDateTime >= @p0 ) q1 ON t0.SessionId = q1.Id', N'@p0 datetime', @p0 = 'Mar 13 2012 12:00:00:000AM'

I get "Invalid column name 'Id'".

I can only get it to work if I change the last line to:

t0.TrainingSessionId = q1."TrainingSession.Id"', N'@p0 datetime', @p0 = 'Mar 13 2012 12:00:00:000AM' or t0.TrainingSessionId = "TrainingSession.Id"', N'@p0 datetime', @p0 = 'Mar 13 2012 12:00:00:000AM'

however I don't know how to generate this SQL from the Lightspeed query.

I followed this example but the SQL that is generated does not execute successfully.

I want all sessionusers where their session.startdatetime > current datetime. The FK relationship is sessionuser.sessionid = session.id

Query queryInner = new Query(typeof(Session)); queryInner.QueryExpression = (Entity.Attribute("StartDateTime") >= DateTime.Now);

Query query = new Query(typeof(SessionUser)); query.Join = Join.Inner(typeof(SessionUser), queryInner, "SessionId", "Id");

unitOfWork.Find(query) ...

This generates SQL like the following:

exec sp_executesql N'SELECT t0.Id AS "t0.Id", t0.SessionId AS "t0.SessionId" FROM SessionUser t0 INNER JOIN (SELECT Session.Id AS "Session.Id", Session.StartDateTime AS "Session.StartDateTime" FROM Session WHERE Session.StartDateTime >= @p0 ) q1 ON t0.SessionId = q1.Id', N'@p0 datetime', @p0 = 'Mar 13 2012 12:00:00:000AM'

I get "Invalid column name 'Id'".

I can only get it to work if I change the last line to:

t0.TrainingSessionId = q1."TrainingSession.Id"', N'@p0 datetime', @p0 = 'Mar 13 2012 12:00:00:000AM'

-or-

t0.TrainingSessionId = "TrainingSession.Id"', N'@p0 datetime', @p0 = 'Mar 13 2012 12:00:00:000AM'

however I don't know how to generate this SQL from the Lightspeed query.

Assuming that SessionUser has a Session association, you should be able to do directly using a dotted path:

uow.Find<SessionUser>(Entity.Attribute("Session.StartDateTime") >= DateTime.Now);

LightSpeed will then generate the required subqueries for you.

This worked perfectly, thank you!

Following the answer, I tried this:

var country = ...;
var orders =
    this.UnitOfWork.Find<Order>(
        new Query
        {
            EntityType = typeof(Order),
            Join =
                Join.Inner(
                    typeof(Order),
                    new Query(
                        typeof(Customer),
                        Entity.Attribute("CountryId") == country.Id),
                    "CustomerId",
                    "Id")
        });

But this returns an empty collection, The inner Customer query returns the proper rows, and the Order query also works by itself, but the join fails.

If I understand your query correctly, you are trying to find orders where the order's Customer.CountryId has a particular value.

You can do this by writing:

unitOfWork.Find<Order>(Entity.Attribute("Customer.CountryId") == country.Id);

We feel this is much more self-documenting (and much easier to write!) than writing the join out longhand.

Ivan,

I can't do that, since "CustomerId" is an int property - not a relationship (it's actually "EntityId" kind of generic relationship column). Hence the need for join.

Tahir

Okay, if you can't use a Customer association, here's an explicit join example that works for me:

UnitOfWork.Find<Comment>(new Query
{
  Join = Join.Inner(
    typeof(Comment),
    new Query(typeof(Contribution), Entity.Attribute("ContributorId") == 42),
    "ContributionId",
    "Id")
});

where Comment has a one-to-many association to Contribution via Comment.ContributionId. This looks pretty much the same as yours though so I'm not sure why yours would be failing.

Could it be the version difference? We're using 3.0.789.12667.

Yes, this is quite possible this would be the difference :) Are you able to look at updating to the latest nightly and checking if that is the case? Alternatively if you are able to send through a small repro project we can test this against the current build for you.

Jeremy