Bug #68513 Error in LINQ to Entities query when using Distinct().Count()

• Submitted: 27 Feb 2013 23:11
• Modified: 28 Jun 2013 2:17
• Reporter: Fernando Gonzalez.Sanchez
• Status: Closed
• Category: Connector / NET
• Severity: S2 (Serious)
• OS: Any
• Assigned to: Fernando Gonzalez.Sanchez
• CPU Architecture: Any

[27 Feb 2013 23:11] Fernando Gonzalez.Sanchez

Description:

This issue was originally reported at forums: http://forums.mysql.com/read.php?38,580094,580094#msg-580094

An error of "Unknown column 'Distinct1.nCdSite' in 'where clause'".

How to repeat:

Using this code first model:

[Table("pagina")] 
  public class pagina 
  { 
    [Key] 
    public long nCdPagina { get; set; } 
    public long nCdVisitante { get; set; } 
    public string sDsUrlReferencia { get; set; } 
    public string sDsPalavraChave { get; set; } 
    public string sDsTitulo { get; set; } 

    [ForeignKey("nCdVisitante")] 
    public visitante visitante { get; set; } 
  } 

  public class retorno 
  { 
    //[Key]
    public long Key { get; set; } 
    public int Online { get; set; } 
  } 

  [Table("site")] 
  public class site 
  { 
    [Key] 
    public long nCdSite { get; set; } 
    public string sDsTitulo { get; set; } 
    public string sDsUrl { get; set; } 
    public DateTime tDtCadastro { get; set; } 
  } 

  [Table("visitante")] 
  public class visitante 
  { 
    [Key] 
    public long nCdVisitante { get; set; }
    public long nCdSite { get; set; }
    public string sDsIp { get; set; }
    public DateTime tDtCadastro { get; set; }
    public DateTime tDtAtualizacao { get; set; }

    [ForeignKey("nCdSite")]
    public site site { get; set; }
  }

  public class SiteDbContext : DbContext
  {
    public DbSet<visitante> Visitante { get; set; }
    public DbSet<site> Site { get; set; }
    //public DbSet<retorno> Retorno { get; set; }
    public DbSet<pagina> Pagina { get; set; }

    public SiteDbContext()
    {
      Database.SetInitializer<SiteDbContext>(new SiteDbInitializer());
    }
  }

  public class SiteDbInitializer : DropCreateDatabaseReallyAlways<SiteDbContext>
  {
  }

And this LINQ query:

var q = (from pag in db.Pagina.Include("visitante").Include("site")
                 group pag by pag.visitante.nCdSite into g
                 select new retorno
                 {
                   Key = g.Key,
                   Online = g.Select(e => e.visitante.sDsIp).Distinct().Count()
                 }).ToList<retorno>();

Suggested fix:

LINQ Query must work without throwing exception.

[2 Apr 2013 22:18] Heinz Meissl

I have the very similar exception "Unknown column 'Extent1.AddressBookId' in 'on clause'" and a similar stack trace. My query joins three tables over two relationships (m:n) and uses any().

I'm using EntityFramework 5.0 and MySQL Connection/Net 6.6.5.

[3 Jun 2013 18:29] Fernando Gonzalez.Sanchez

Hi Heinz,

Thanks for your feedback, can you provide a concrete sample? 

This bug is specific to Distinct().Count().

Regards.

[28 Jun 2013 2:17] Philip Olson

Fixed as of the upcoming Connector/Net 6.5.7, 6.6.6, and 6.7.4 releases, and here's the changelog entry:

The "LINQ" query would return the error "An error of "Unknown column
'Distinct1.nCdSite' in 'where clause'" when using "Distinct().Count()".

Thank you for the bug report.