locked
Converting a MySQL query to LINQ troubles RRS feed

  • Question

  • I am using MySQL and LINQ with C# 4.0.  I am trying to convert this MySQL query to LINQ and am having a bit of trouble.

    1:
    2:
    3:
    4:
    5:
    6:
    7:
    8:
    9:
    10:
    11:
    
    SELECT tblReferenceSMTPAccounts.smtpEmailID
    FROM tblReferenceSMTPAccounts
    LEFT JOIN (
      SELECT smtpEmailID, COUNT(*) as uses
      FROM tblEmailQueue
      GROUP BY smtpEmailID
    ) as smtpUses
    ON smtpUses.smtpEmailID = tblReferenceSMTPAccounts.smtpEmailID
    WHERE (smtpUses.uses IS NULL) OR (smtpUses.uses < tblReferenceSMTPAccounts.smtpDailyMax)
    ORDER BY smtpUses.uses
    LIMIT 0,1
    


    Here is my LINQ statement.

    1:
    2:
    3:
    4:
    5:
    6:
    7:
    8:
    9:
    10:
    
    var query = (from c in DBEntities.tblReferenceSMTPAccounts
    	join d in
    		(from e in DBEntities.tblEmailQueues
    		group e by e.smtpEmailID into f
    		select new { smtpEmailID = f.Key, Count = f.Count() }
    		) on c.smtpEmailID equals d.smtpEmailID into g
    	from h in g
    	where c.active == true && (int)h.Count <= c.smtpDailyMax
    	orderby h.Count ascending
    	select new { c.smtpEmailID }).FirstOrDefault();
    


    It is producing this error:

    System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> MySql.Data.MySqlClient.MySqlException: Unknown column 'GroupBy1.K1' in 'on clause' at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

    Any ideas?

    John Chapman | Blog: http://www.johnchapman.name
    • Moved by Alex Liang Tuesday, October 12, 2010 3:29 AM (From:LINQ to SQL)
    Tuesday, September 21, 2010 1:14 AM

All replies

  • First of all, I'm assuming you're using Entity Framework (not Linq-to-SQL)..? If so, the EF forum is probably a better place to ask this question: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/threads/

    That said, based on the error I would guess there is either a mapping problem (column removed?) or a problem with the MySQL provider you're using...
     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Tuesday, September 21, 2010 2:49 AM
  • Hi John,

     

    Since Microsoft .NET BCL does not provide any provider to let developer connect to MySql database, I think you could get some better support on MySql forums.

    http://forums.mysql.com/list.php?38

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, September 21, 2010 8:53 AM
  • I don't like this statement...

    (int)h.Count

    First h.Count should already be an Int32. Second if you need to convert, you should use the static Convert methods IE Convert.ToInt32(h.Count).

    Tuesday, September 21, 2010 6:38 PM