I'm having trouble with the following query:

-- select all messages where both the sender and recipient are currently active
select m.* from messages m join users u1 on u1.UserId = m.SenderId join users u2 on u2.UserId = m.RecipientId
where u1.IsActive = 1 and u2.IsActive=1

This does not work:

IRelationPredicateBucket b = new RelationPredicateBucket();
b.Relations.Add(MessageEntity.Relations.UserEntityUsingFkSenderUserId);
b.Relations.Add(MessageEntity.Relations.UserEntityUsingFkRecipientUserId);

IPredicateExpression pe = new PredicateExpression(UserFields.IsActive == true);
IPrefetchPath2 p = new PrefetchPath2((int)EntityType.MessageEntity);
p.Add(FriendEntity.PrefetchPathSendingUser, 0, pe);
p.Add(FriendEntity.PrefetchPathReceivingUser, 0, pe);

c = new MessageCollection();
da.FetchEntityCollection(c, b,p);

Presently I'm resorting to typed views because the query is SO much easier to write than the LLBL code.

In these 2 lines of code:

p.Add(FriendEntity.PrefetchPathSendingUser, 0, pe);
p.Add(FriendEntity.PrefetchPathReceivingUser, 0, pe);

...what is the FriendEntity? I don't see a table called "Friend" in your query example.

Chester wrote:

In these 2 lines of code:

p.Add(FriendEntity.PrefetchPathSendingUser, 0, pe);
p.Add(FriendEntity.PrefetchPathReceivingUser, 0, pe);

...what is the FriendEntity? I don't see a table called "Friend" in your query example.

Sorry, I "simplified" the example

The real code is this, which works now:

l = new FriendCollection();

IRelationPredicateBucket b = new RelationPredicateBucket();
b.PredicateExpression.Add(FriendFields.FkOwningUserId == mOwnerId);
b.Relations.Add(FriendEntity.Relations.UserEntityUsingFkFriendUserId, "u1").CustomFilter=new PredicateExpression(UserFields.IsEnabled.SetObjectAlias("u1")==true);
b.Relations.Add(FriendEntity.Relations.UserEntityUsingFkOwningUserId, "u2").CustomFilter=new PredicateExpression(UserFields.IsEnabled.SetObjectAlias("u2")==true);

IPrefetchPath2 p = new PrefetchPath2((int)EntityType.FriendEntity);
p.Add(FriendEntity.PrefetchPathFriendUser);
p.Add(FriendEntity.PrefetchPathOwningUser);

da.FetchEntityCollection(l, b,p);

All of that takes the place of a simple query:

select
   f.*
from
   friend f join user u1 on u1.userId = f.FkOwningUserId
   join user u2 on u2.userId = f.FriendUserId
where
   u1.isEnabled = 1 and u2.isEnabled = 1

Since I fixed it this is kind of a side note at this point, but I have to ask: what is the benefit of LLBL in a situation like this? It was incredibly complicated to build that query and incredibly easy to write it in SQL.

If you've been writing SQL for years then it will be easier at first to just write SQL. But the learning curve in my opinion isn't that steep once you understand what all the constructs are doing. As to the advantages of using LLBLGen, they are many.

I'd suggest doing some reading on O/R mappers in general - the LLBLGen documentation is a great place to start if you haven't read it through already.