Query Builder using LINQ/Extension

Posts   
 
    
dodyg
User
Posts: 42
Joined: 04-Dec-2014
# Posted on: 13-Feb-2017 14:29:42   

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

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 13-Feb-2017 17:19:05   

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));      
        }

dodyg
User
Posts: 42
Joined: 04-Dec-2014
# Posted on: 14-Feb-2017 10:08:01   

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.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-Feb-2017 17:15:58   

So does my recommendation work as you want ?

dodyg
User
Posts: 42
Joined: 04-Dec-2014
# Posted on: 15-Feb-2017 10:25:04   

Yes. Thanks.