none
Entity framework 4.3.1 relation one to zero or one. RRS feed

  • Question

  •     using System.Data.Entity;
        using System.Linq;
    
        public class ZoneMedia
        {
            public int ZoneMediaID { get; set; }
            public string MediaName { get; set; }
            public int Width { get; set; }
            public int Height { get; set; }
    
            public virtual ZoneMediaText MediaText { get; set; }
        }
    
        public class ZoneMediaText
        {
            public int ZoneMediaID { get; set; }
            public string Text { get; set; }
            public int Color { get; set; }
    
            public virtual ZoneMedia ZoneMedia { get; set; }
        }
    
        public class TestEFDbContext : DbContext
        {
            public DbSet<ZoneMedia> ZoneMedia { get; set; }
            public DbSet<ZoneMediaText> ZoneMediaText { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<ZoneMedia>()
                    .HasOptional(zm => zm.MediaText);
                modelBuilder.Entity<ZoneMediaText>()
                    .HasKey(zmt => zmt.ZoneMediaID);
                modelBuilder.Entity<ZoneMediaText>()
                    .HasRequired(zmt => zmt.ZoneMedia)
                    .WithRequiredDependent(zm => zm.MediaText);
    
                base.OnModelCreating(modelBuilder);
            }
        }
        class Program
        {
            static void Main(string[] args)
            {
                var dbcontext = new TestEFDbContext();
                var l = dbcontext.ZoneMedia.ToList();
                var media =
                    dbcontext.ZoneMedia.FirstOrDefault(
                        zm => zm.MediaText != null && zm.MediaText.Text != null && zm.MediaText.Text.Length > 0 && zm.MediaText.Color>4);
                media =
                    dbcontext.ZoneMedia.FirstOrDefault(
                        zm => zm.MediaText.Text.Length > 0 && zm.MediaText.Color > 4);
    
            }
        }
    

    The last 2 FirstOrDefault generate this respectively:

    	SELECT TOP (1) 
    	[Filter2].[ZoneMediaID1] AS [ZoneMediaID], 
    	[Filter2].[MediaName] AS [MediaName], 
    	[Filter2].[Width] AS [Width], 
    	[Filter2].[Height] AS [Height]
    	FROM   (SELECT [Filter1].[ZoneMediaID1], [Filter1].[MediaName], [Filter1].[Width], [Filter1].[Height], [Filter1].[ZoneMediaID2]
    		FROM   (SELECT [Extent1].[ZoneMediaID] AS [ZoneMediaID1], [Extent1].[MediaName] AS [MediaName], [Extent1].[Width] AS [Width], [Extent1].[Height] AS [Height], [Extent2].[ZoneMediaID] AS [ZoneMediaID2]
    			FROM    [dbo].[ZoneMedias] AS [Extent1]
    			LEFT OUTER JOIN [dbo].[ZoneMediaTexts] AS [Extent2] ON [Extent1].[ZoneMediaID] = [Extent2].[ZoneMediaID]
    			INNER JOIN [dbo].[ZoneMediaTexts] AS [Extent3] ON [Extent2].[ZoneMediaID] = [Extent3].[ZoneMediaID]
    			INNER JOIN [dbo].[ZoneMediaTexts] AS [Extent4] ON [Extent2].[ZoneMediaID] = [Extent4].[ZoneMediaID]
    			WHERE (cast(1 as bit) <> cast(0 as bit)) AND ([Extent4].[Text] IS NOT NULL) ) AS [Filter1]
    		LEFT OUTER JOIN [dbo].[ZoneMediaTexts] AS [Extent5] ON [Filter1].[ZoneMediaID2] = [Extent5].[ZoneMediaID]
    		WHERE ( CAST(LEN([Extent5].[Text]) AS int)) > 0 ) AS [Filter2]
    	INNER JOIN [dbo].[ZoneMediaTexts] AS [Extent6] ON [Filter2].[ZoneMediaID2] = [Extent6].[ZoneMediaID]
    	WHERE [Extent6].[Color] > 4
    	 
    
    	SELECT TOP (1) 
    	[Filter1].[ZoneMediaID1] AS [ZoneMediaID], 
    	[Filter1].[MediaName] AS [MediaName], 
    	[Filter1].[Width] AS [Width], 
    	[Filter1].[Height] AS [Height]
    	FROM   (SELECT [Extent1].[ZoneMediaID] AS [ZoneMediaID1], [Extent1].[MediaName] AS [MediaName], [Extent1].[Width] AS [Width], [Extent1].[Height] AS [Height], [Extent2].[ZoneMediaID] AS [ZoneMediaID2]
    		FROM   [dbo].[ZoneMedias] AS [Extent1]
    		LEFT OUTER JOIN [dbo].[ZoneMediaTexts] AS [Extent2] ON [Extent1].[ZoneMediaID] = [Extent2].[ZoneMediaID]
    		LEFT OUTER JOIN [dbo].[ZoneMediaTexts] AS [Extent3] ON [Extent2].[ZoneMediaID] = [Extent3].[ZoneMediaID]
    		WHERE ( CAST(LEN([Extent3].[Text]) AS int)) > 0 ) AS [Filter1]
    	INNER JOIN [dbo].[ZoneMediaTexts] AS [Extent4] ON [Filter1].[ZoneMediaID2] = [Extent4].[ZoneMediaID]
    	WHERE [Extent4].[Color] > 4
    


    What's wrong with it? is it a bug? What's the purpose of these multiple inner/left joins?

    Monday, July 23, 2012 8:16 AM

Answers

  • To you statement, "I believe this is a bug", It is not a bug. Linq to Entities and Linq to SQL are a query language that queries a conceptual model and the Entity Framework translate it as you see it. At the current time it is the best it can do. If that is not satisfactory you can always create a stored procedure and execute it through Entity Framework. If you still believe that it is a bug you can go to Microsoft Connect to post issues with products.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Alexander Sun Friday, August 3, 2012 2:25 AM
    Tuesday, July 24, 2012 3:52 AM

All replies

  • Hi Alex23571;

    See if this gives you a more efficent query. This query look through the ZoneMediaText table finding all records that match the criteria and returns a ZoneMedia for the record found.

    var media = dbcontext.ZoneMediaText.FirstOrDefault(zt => 
        zt.Text != null 
        && zt.Text.Length > 0 
        && zt.Color > 4 
        && zt.ZoneMedia != null).ZoneMedia;

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, July 23, 2012 5:31 PM
  • It makes a bit better, BUT 1 drawback is that it makes 2 queries if it can't find object in the tracker. So when I comment all lines,

    especially 

    var l = dbcontext.ZoneMedia.ToList();

    that query makes 2 queries. But it could do in 1 query.

    var media2 = dbcontext.ZoneMediaText.FirstOrDefault(zt =>
                    zt.Text != null
                    && zt.Text.Length > 0
                    && zt.Color > 0
                    && zt.ZoneMedia != null).ZoneMedia;

    Actually, I needed that query

    var f100 = dbcontext.ZoneMedia.Where(zm => zm.MediaText == null).OrderBy(zm => zm.ZoneMediaID).Take(100);

    And it generated that query:

    SELECT TOP (100) 
    [Extent1].[ZoneMediaID] AS [ZoneMediaID], 
    [Extent1].[MediaName] AS [MediaName], 
    [Extent1].[Width] AS [Width], 
    [Extent1].[Height] AS [Height]
    FROM   [dbo].[ZoneMedias] AS [Extent1]
    LEFT OUTER JOIN [dbo].[ZoneMediaTexts] AS [Extent2] ON [Extent1].[ZoneMediaID] = [Extent2].[ZoneMediaID]
    LEFT OUTER JOIN [dbo].[ZoneMediaTexts] AS [Extent3] ON [Extent2].[ZoneMediaID] = [Extent3].[ZoneMediaID]
    WHERE [Extent3].[ZoneMediaID] IS NULL
    ORDER BY [Extent1].[ZoneMediaID] ASC

    The query otimizer shows additional join which actually not requried.

    I believe this is a bug, query builder before adding a join should check if the join was added and use exising one instead of adding new.


    • Edited by Alex23571 Tuesday, July 24, 2012 3:25 AM type error in the first line instead of "when I commit" -> "when I comment"
    Tuesday, July 24, 2012 12:27 AM
  • To you statement, "I believe this is a bug", It is not a bug. Linq to Entities and Linq to SQL are a query language that queries a conceptual model and the Entity Framework translate it as you see it. At the current time it is the best it can do. If that is not satisfactory you can always create a stored procedure and execute it through Entity Framework. If you still believe that it is a bug you can go to Microsoft Connect to post issues with products.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Alexander Sun Friday, August 3, 2012 2:25 AM
    Tuesday, July 24, 2012 3:52 AM