Remove From My Forums

Converting a MySQL query to LINQ troubles

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

0

Sign in to vote

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
0

Sign in to vote

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

Asked by:

Converting a MySQL query to LINQ troubles

Question

All replies