none
Same table appearing twice on SQL query RRS feed

  • Question

  • Hi,

    I have a very simple linqToEntities query:


                var result = (from r in ctx.ReferralsClients.OfType<ReferralsClient>()
                    where r.Client.ClientName == "Abus Mobile Security Inc. (USA)"
                              select r).FirstOrDefault();


    It should be translated as a simple relationship between ReferralsClient and Client table, but when this query is executed the ReferralsClients appears twice in the query.

    The resulting query is like this:

    exec sp_executesql N'SELECT TOP (1) 
        [Extent1].[ClientType] AS [ClientType], 
        [Extent1].[ClientId] AS [ClientId], 
        [Extent1].[ClientStatusId] AS [ClientStatusId], 
        [Extent1].[OwningSPFirmId] AS [OwningSPFirmId], 
        [Extent1].[InternalReference] AS [InternalReference], 
        [Extent1].[Notes] AS [Notes], 
        [Extent1].[SharedWorkId] AS [SharedWorkId]
        FROM  [Referrals].[ReferralsClient] AS [Extent1]
        INNER JOIN  (SELECT [Extent2].[ClientName] AS [ClientName], [Extent3].[ClientId] AS [ClientId1]
            FROM  [ConnectPlus].[Client] AS [Extent2]
            INNER JOIN [Referrals].[ReferralsClient] AS [Extent3] ON ([Extent3].[ClientType] IN (1,2)) AND ([Extent2].[ClientId] = [Extent3].[ClientId])
            WHERE 1 = 1 ) AS [Filter1] ON [Extent1].[ClientId] = [Filter1].[ClientId1]
        WHERE ([Extent1].[ClientType] IN (1,2)) AND (([Filter1].[ClientName] = @p__linq__0) OR (1 = 0))',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'Abus Mobile Security Inc. (USA)'


    The OfType is not making any difference, I already tried with and without it. However, if I add an include to the query, instead of repeating ReferralsClient twice, the query repeats Client table twice.

                var result = (from r in ctx.ReferralsClients.OfType<ReferralsClient>().Include(x=>x.Client)
                    where r.Client.ClientName == "Abus Mobile Security Inc. (USA)"
                              select r).FirstOrDefault();

    The result:

    SELECT TOP (1) 
        [Extent1].[ClientId] AS [ClientId], 
        [Extent1].[ClientType] AS [ClientType], 
        [Extent1].[ClientStatusId] AS [ClientStatusId], 
        [Extent1].[OwningSPFirmId] AS [OwningSPFirmId], 
        [Extent1].[InternalReference] AS [InternalReference], 
        [Extent1].[Notes] AS [Notes], 
        [Extent1].[SharedWorkId] AS [SharedWorkId], 
        [Extent3].[ClientId] AS [ClientId1], 
        [Extent3].[ClientName] AS [ClientName], 
        [Extent3].[IsListedOnGRT] AS [IsListedOnGRT], 
        [Extent3].[ClientGroupId] AS [ClientGroupId], 
        [Extent3].[SharepointId] AS [SharepointId]
        FROM   [Referrals].[ReferralsClient] AS [Extent1]
        INNER JOIN [ConnectPlus].[Client] AS [Extent2] ON ([Extent1].[ClientId] = [Extent2].[ClientId]) AND ([Extent1].[ClientType] IN (1,2))
        LEFT OUTER JOIN [ConnectPlus].[Client] AS [Extent3] ON ([Extent3].[ClientId] = [Extent1].[ClientId]) AND ([Extent1].[ClientType] IN (1,2))
        WHERE (N'Abus Mobile Security Inc. (USA)' = [Extent2].[ClientName]) AND ([Extent1].[ClientType] IN (1,2))


    How can I avoid this duplication when generating the SQL query?

    I'm using Entity Framework 6.1.3


    Thank you in advance,


    Dennes


    • Edited by Dennes Monday, July 9, 2018 2:26 PM
    Monday, July 9, 2018 2:25 PM

All replies

  • Hi Dennes,

    As far as I know, we could't affect the SQL generated by the entity framework, if we could execute SQL statement as excepted, we could use entity framework raw SQL, for more information about entity framework raw SQL, please refer to:

    https://msdn.microsoft.com/en-us/library/jj592907%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 10, 2018 7:43 AM
    Moderator
  • Zhanglong,

    Yes, I know about that.

    However, this query generation seems to me to be a mistake, almost a bug which takes 40% of the query processing time (Am I wrong? Could you explain if I am wrong?)

    I was willing to find some hack to change the query, the model or some model configuration to avoid this, there are model configurations which affect the query generation.

    Regards,

    Dennes


    Dennes [http://bufaloinfo.cloudapp.net] Inscreva-se em meu treinamento on-line de T-SQL - [http://bufaloinfo.cloudapp.net/Cursos/linguagemsql.aspx]

    Tuesday, July 10, 2018 7:52 AM
  • Hi,

    Additional information, almost a solution:

    I discovered that inverting the query, the SQL generated by the query becomes correct:

                             from r in ctx.Clients
                              where r.ClientName == "client name appears here"
                              select r.ReferralsClient

    Instead of querying ReferralsClient and testing the clientname field, the query is over Clients and bring the ReferralsClient as a result.

    The relation between ReferralsClient and Client is a 1-1 relation with the foreign key on ReferralsClient. Basically, in a 1-1 relation if I query one table and make a filter on a field on the other table, the filter becomes a complete sub-query on the T-SQL code, duplicating one of the tables.

    I made a test on a 1-N relationship and this doesn't happen, it's only in a 1-1 relationship. 

    Although this seems a solution, I would bet that I will face this situation again in more complex queries, where I will not be able to invert the query like this.

    Is this a bug? Is there another solution that I haven't figured out?

    Thank you!

    Dennes

    Tuesday, July 10, 2018 7:54 AM
  • Hi Dennes,

    Based on your description, I create a simple demo with entity framework 6.2 as below, which works as well. Could you please share a complete sample, which could reproduce the issue.

    #Models and DbContext (One-To-One).

    using System;
    using System.ComponentModel.DataAnnotations;
    using System.Data.Entity;
    
    namespace ConsoleApp15
    {
        public class Client
        {
            [Key]
            public int ClientId { get; set; }
            public string ClientType { get; set; }
    
            public string ClientName { get; set; }
    
            public virtual ReferralsClient ReferralsClient { get; set; }
        }
    
        public class ReferralsClient
        {
            [Key]
            public int ClientId { get; set; }
    
            public int ClientStatusId { get; set; }
    
            public string InternalReference { get; set; }
            public string Notes { get; set; }
         
            public virtual Client Client { get; set; }
        }
    
        public class Db : DbContext
        {
            public Db() : base("MyDb")
            {
                this.Database.Log = Console.WriteLine;
            }
            public virtual DbSet<Client> Clients { get; set; }
            public virtual DbSet<ReferralsClient> ReferralsClients {  get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Client>()
                            .HasRequired(s => s.ReferralsClient)
                            .WithRequiredPrincipal(ad => ad.Client);
    
            }
        }
    }
    

    #Usage

    using System;
    using System.Linq;
    
    namespace ConsoleApp15
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var ctx = new Db())
                {
                    var result = (from r in ctx.ReferralsClients.OfType<ReferralsClient>()
                                  where r.Client.ClientName == "Abus Mobile Security Inc. (USA)"
                                  select r).FirstOrDefault();
                    Console.WriteLine("OK");
    
                    Console.ReadLine();
                }
            }
        }
    }
    

    #Result.

    Best regards,

    Zhanglong 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 11, 2018 2:39 AM
    Moderator
  • Hi, Zhanglong,

    I will make some tests with your sample and get back soon. At this moment the only difference I can notice is that you made the properties virtual. Since I have no intention to use lazy load, I haven't marked them as virtual, I'm not sure if this could be causing this difference, I will check.

    Regards,

    Dennes

    • Edited by Dennes Wednesday, July 11, 2018 7:14 AM
    Wednesday, July 11, 2018 7:14 AM
  • Hi, Zhanglong,

    I used your demo code to make some tests and finally, I figured out what causes the problem. Your demo code is missing the inheritance configuration.

    Just adding a new class to your example:

            public class SharedWorkClient : ReferralsClient
            {
            }


    and configuring the mapping:

                    modelBuilder.Entity<ReferralsClient>()
                        .Map<ReferralsClient>(m => m.Requires("ClientType").HasValue(1))
                        .Map<SharedWorkClient>(m => m.Requires("ClientType").HasValue(2));

    The problem appears, including the clients table twice on the generated T-SQL code.


    Is there something wrong with this mapping that could be avoided and solve this problem?

    Thank you!

    • Edited by Dennes Wednesday, July 11, 2018 11:57 AM
    Wednesday, July 11, 2018 11:57 AM