- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Query Builder using LINQ/Extension
Joined: 04-Dec-2014
I am using LLBLGen 5.1 and Adapter.
public class ThreadFilter : IFilter<MessageEntity>
{
public int? ThreadId { get; set; }
public int? ReceiverUserId { get; set; }
public IQueryable<MessageEntity> Filter(IQueryable<MessageEntity> query)
{
if (ThreadId.HasValue)
query = query.Where(x => x.Id == ThreadId);
if (ReceiverUserId.HasValue)
query = query.Where(x => x.MessageReceivers.First().ReceiverUserId == ReceiverUserId);
return query;
}
}
I am trying to filter by relation using IQueryable. I don't think I am doing this right because the query generated by this is just weird. I had to resort to First() because otherwise I cannot express filtering by a related entity's property.
Is there any better way to do this?
exec sp_executesql N'
SELECT TOP(@p2) [LPA_L2].[Body], [LPA_L2].[DateCreated], [LPA_L2].[Id], 1 AS [LPFA_4], 1 AS [LPFA_5], [LPA_L1].[ArabicName], [LPA_L1].[EnglishName], [LPA_L1].[Id] AS [Id0], [LPA_L1].[IsActive], [LPA_L2].[MessageTypeId], [LPA_L3].[CreatedByUserId], [LPA_L3].[DateCreated] AS [DateCreated1], [LPA_L3].[Email], [LPA_L3].[Id] AS [Id2], [LPA_L3].[IsActive] AS [IsActive3], [LPA_L3].[LastModified], [LPA_L3].[LastModifiedByUserId], [LPA_L3].[Password], [LPA_L4].[Address], [LPA_L4].[AddressArea], [LPA_L4].[AddressCityId], [LPA_L4].[AddressPostCode], [LPA_L4].[BirthDate], [LPA_L4].[CreatedByUserId] AS [CreatedByUserId4], [LPA_L4].[DateCreated] AS [DateCreated5], [LPA_L4].[Email] AS [Email6], [LPA_L4].[FirstName], [LPA_L4].[Gender], [LPA_L4].[Id] AS [Id7], [LPA_L4].[IsActive] AS [IsActive8], [LPA_L4].[LastModified] AS [LastModified9], [LPA_L4].[LastModifiedByUserId] AS [LastModifiedByUserId10], [LPA_L4].[LastName], [LPA_L4].[MobilePhone], [LPA_L4].[ProfilePicturePath], [LPA_L3].[PersonId], [LPA_L2].[SenderUserId], [LPA_L2].[Title]
FROM ((( [MessageType] [LPA_L1]
INNER JOIN [Message] [LPA_L2] ON [LPA_L1].[Id]=[LPA_L2].[MessageTypeId])
INNER JOIN [User] [LPA_L3] ON [LPA_L3].[Id]=[LPA_L2].[SenderUserId])
INNER JOIN [Person] [LPA_L4] ON [LPA_L4].[Id]=[LPA_L3].[PersonId]) WHERE ( ( ( ( ( ( ( ( (SELECT TOP(@p6) [LPLA_2].[ReceiverUserId] FROM [MessageReceiver] [LPLA_2]
WHERE ( ( ( [LPA_L2].[Id] = [LPLA_2].[MessageId])))) = @p4))))))))',N'@p2 bigint,@p6 bigint,@p4 int',@p2=10,@p6=1,@p4=2
exec sp_executesql N'
SELECT [LPA_L2].[DateRead], [LPA_L2].[Id], [LPA_L2].[IsRead], [LPA_L2].[MessageId], [LPA_L2].[ReceiverUserId], [LPA_L1].[CreatedByUserId], [LPA_L1].[DateCreated], [LPA_L1].[Email], [LPA_L1].[Id] AS [Id0], [LPA_L1].[IsActive], [LPA_L1].[LastModified], [LPA_L1].[LastModifiedByUserId], [LPA_L1].[Password], [LPA_L3].[Address], [LPA_L3].[AddressArea], [LPA_L3].[AddressCityId], [LPA_L3].[AddressPostCode], [LPA_L3].[BirthDate], [LPA_L3].[CreatedByUserId] AS [CreatedByUserId1], [LPA_L3].[DateCreated] AS [DateCreated2], [LPA_L3].[Email] AS [Email3], [LPA_L3].[FirstName], [LPA_L3].[Gender], [LPA_L3].[Id] AS [Id4], [LPA_L3].[IsActive] AS [IsActive5], [LPA_L3].[LastModified] AS [LastModified6], [LPA_L3].[LastModifiedByUserId] AS [LastModifiedByUserId7], [LPA_L3].[LastName], [LPA_L3].[MobilePhone], [LPA_L3].[ProfilePicturePath], [LPA_L1].[PersonId]
FROM (( [User] [LPA_L1] INNER JOIN [MessageReceiver] [LPA_L2] ON [LPA_L1].[Id]=[LPA_L2].[ReceiverUserId]) INNER JOIN [Person] [LPA_L3] ON [LPA_L3].[Id]=[LPA_L1].[PersonId])
WHERE ( ( ( [LPA_L2].[MessageId] = @p1)))',N'@p1 int',@p1=1
exec sp_executesql N'SELECT [LPA_L2].[Body], [LPA_L2].[DateCreated], [LPA_L2].[DateRead] AS [DateTimeRead], [LPA_L2].[Id], [LPA_L2].[IsRead], [LPA_L2].[MessageId], [LPA_L1].[CreatedByUserId],
[LPA_L1].[DateCreated] AS [DateCreated0], [LPA_L1].[Email], [LPA_L1].[Id] AS [Id1], [LPA_L1].[IsActive], [LPA_L1].[LastModified], [LPA_L1].[LastModifiedByUserId], [LPA_L1].[Password],
[LPA_L3].[Address], [LPA_L3].[AddressArea], [LPA_L3].[AddressCityId], [LPA_L3].[AddressPostCode], [LPA_L3].[BirthDate], [LPA_L3].[CreatedByUserId] AS [CreatedByUserId2], [LPA_L3].[DateCreated] AS
[DateCreated3], [LPA_L3].[Email] AS [Email4], [LPA_L3].[FirstName], [LPA_L3].[Gender], [LPA_L3].[Id] AS [Id5], [LPA_L3].[IsActive] AS [IsActive6], [LPA_L3].[LastModified] AS [LastModified7], [LPA_L3].[LastModifiedByUserId] AS [LastModifiedByUserId8], [LPA_L3].[LastName], [LPA_L3].[MobilePhone], [LPA_L3].[ProfilePicturePath], [LPA_L1].[PersonId], [LPA_L2].[ReceiverUserId], [LPA_L4].[CreatedByUserId] AS [CreatedByUserId9], [LPA_L4].[DateCreated] AS [DateCreated10], [LPA_L4].[Email] AS [Email11], [LPA_L4].[Id] AS [Id12], [LPA_L4].[IsActive] AS [IsActive13], [LPA_L4].[LastModified] AS [LastModified14], [LPA_L4].[LastModifiedByUserId] AS [LastModifiedByUserId15], [LPA_L4].[Password] AS [Password16], [LPA_L5].[Address] AS [Address17], [LPA_L5].[AddressArea] AS [AddressArea18], [LPA_L5].[AddressCityId] AS [AddressCityId19], [LPA_L5].[AddressPostCode] AS [AddressPostCode20], [LPA_L5].[BirthDate] AS [BirthDate21], [LPA_L5].[CreatedByUserId] AS [CreatedByUserId22], [LPA_L5].[DateCreated] AS [DateCreated23], [LPA_L5].[Email] AS [Email24], [LPA_L5].[FirstName] AS [FirstName25], [LPA_L5].[Gender] AS [Gender26], [LPA_L5].[Id] AS [Id27], [LPA_L5].[IsActive] AS [IsActive28], [LPA_L5].[LastModified] AS [LastModified29], [LPA_L5].[LastModifiedByUserId] AS [LastModifiedByUserId30], [LPA_L5].[LastName] AS [LastName31], [LPA_L5].[MobilePhone] AS [MobilePhone32], [LPA_L5].[ProfilePicturePath] AS [ProfilePicturePath33], [LPA_L4].[PersonId] AS [PersonId34], [LPA_L2].[SenderUserId]
FROM
(((( [User] [LPA_L1]
INNER JOIN [MessageReply] [LPA_L2] ON [LPA_L1].[Id]=[LPA_L2].[ReceiverUserId])
INNER JOIN [Person] [LPA_L3] ON [LPA_L3].[Id]=[LPA_L1].[PersonId]) INNER JOIN [User] [LPA_L4] ON [LPA_L4].[Id]=[LPA_L2].[SenderUserId]) INNER JOIN [Person] [LPA_L5] ON [LPA_L5].[Id]=[LPA_L4].[PersonId])
WHERE ( ( ( [LPA_L2].[MessageId] = @p1)))',N'@p1 int',@p1=1
Joined: 21-Aug-2005
Very much easier with QuerySpec.
I assume you need to do something like:
select * from Orders
where orderId in
(select orderId from [Order Details] where productid = 1)
Which can be acheived using:
// This is the one you need to extract in a method, passing back a DynamicQuery or an IQuerySpec.
var q = qf.Create();
q.Select(OrderDetailFields.OrderId)
.Where(OrderDetailFields.ProductId == 1);
//////
var qOrders = qf.Order
.Where(OrderFields.OrderId.In(q));
LinQ example:
private void TestLinqSubSelect()
{
using (var adapter = new DataAccessAdapter())
{
var metaData = new LinqMetaData(adapter);
var q = from o in metaData.Order
select o;
var result = Filter(q, metaData).ToList();
}
}
public IQueryable<OrderEntity> Filter(IQueryable<OrderEntity> query, LinqMetaData metaData)
{
var qDetails = from od in metaData.OrderDetail
where od.ProductId == 1
select od.OrderId;
return query.Where(o => qDetails.Contains(o.OrderId));
}
Joined: 04-Dec-2014
I do some more digging and here's the result of generated using CountAsync so we get smaller SQL
This code (my original)
public class ThreadFilter : IFilter<MessageEntity>
{
public int? ThreadId { get; set; }
public int? ReceiverUserId { get; set; }
public IQueryable<MessageEntity> Filter(IQueryable<MessageEntity> query)
{
if (ThreadId.HasValue)
query = query.Where(x => x.Id == ThreadId);
if (ReceiverUserId.HasValue)
query = query.Where(x => x.MessageReceivers.First().ReceiverUserId == ReceiverUserId);
return query;
}
}
produces the following SQL
exec sp_executesql N'SELECT TOP(@p2) COUNT(*) AS [LPAV_] FROM [Inspection].[dbo].[Message] [LPLA_1] WHERE ( ( ( ( (SELECT TOP(@p6) [LPLA_2].[ReceiverUserId] FROM [Inspection].[dbo].[MessageReceiver] [LPLA_2] WHERE ( ( ( [LPLA_1].[Id] = [LPLA_2].[MessageId])))) = @p4))))',N'@p2 bigint,@p6 bigint,@p4 int',@p2=1,@p6=1,@p4=2
Using your recommendation
public class ThreadFilter : IFilter<MessageEntity>
{
public int? ThreadId { get; set; }
public int? ReceiverUserId { get; set; }
public IQueryable<MessageEntity> Filter(IQueryable<MessageEntity> query, LinqMetaData meta = null)
{
if (ThreadId.HasValue)
query = query.Where(x => x.Id == ThreadId);
if (ReceiverUserId.HasValue)
{
if (meta == null)
throw new ArgumentNullException($"{meta} must be supplied because it is to be used");
var receivers = from x in meta.MessageReceiver
where x.ReceiverUserId == ReceiverUserId
select x.MessageId;
query = query.Where(x => receivers.Contains(x.Id));
}
return query;
}
}
returns
exec sp_executesql N'SELECT TOP(@p2) COUNT(*) AS [LPAV_] FROM [Inspection].[dbo].[Message] [LPLA_1] WHERE ( ( ( EXISTS (SELECT [LPA_L1].[MessageId] FROM (SELECT [LPLA_2].[MessageId] FROM [Inspection].[dbo].[MessageReceiver] [LPLA_2] WHERE ( ( ( [LPLA_2].[ReceiverUserId] = @p3)))) [LPA_L1] WHERE ( [LPA_L1].[MessageId] = [LPLA_1].[Id])))))',N'@p2 bigint,@p3 int',@p2=1,@p3=2
If I clean up the code, the first approach is
select *
from Message a
where ((select top 1 ReceiverUserId from MessageReceiver b where a.Id = b.MessageId) = @ReceiverUserId)
and the second approach is
select *
from Message a
where exists(
select b.MessageId
from (select MessageId from MessageReceiver where ReceiverUserId = @ReceiverUserId) b
where b.MessageId = a.Id
)
So yeah my original approach produces SQL that will go wrong if there are more than one ReceiverUserId on the a MessageId.
Thanks.