Error when defining a query param type List<int> inline using Linq

Question

I am not sure if this is a bug or not, but am getting an error and I am not seeing what the cause is. I have a method that takes an argument that takes a query param.

public static List<EmailNotification> GetEmailNotifications (EmailNotificationQuery query)
{
    using (HalDataContext hal = new HalDataContext())
    {
        IQueryable<EmailNotification> emailNotifications = hal.EmailNotifications;

        if (query.EmailNotificationID != null)
        {
            emailNotifications = emailNotifications.Where(en => en.EmailNotificationID == query.EmailNotificationID);
        }

        if (query.EmailNotificationIDs.Count > 0)
        {
            emailNotifications = emailNotifications.Where(en => en.EmailNotificationID.In(query.EmailNotificationIDs));
        }

        if (query.EmailNotificationTemplateID != null)
        {
            emailNotifications = emailNotifications.Where(en => en.EmailNotificationTemplateID == query.EmailNotificationTemplateID);
        }

        //this was causing error!!!!!!!!!!!!!!!!
        if (query.EmailNotificationTemplateIDs.Count > 0)
        {
            emailNotifications = emailNotifications.Where(en => en.EmailNotificationTemplateID.In(query.EmailNotificationTemplateIDs);
        }

        return emailNotifications.ToList();
    }
}

And the Query class

public class EmailNotificationQuery
{
    public EmailNotificationQuery ()
    {
        EmailNotificationIDs = new List<int>();
    }

    public List<int> EmailNotificationIDs { get; set; }
    public int? EmailNotificationID { get; set; }
    public int? EmailNotificationTemplateID { get; set; }
    public List<int> EmailNotificationTemplateIDs { get; set; }
}

When I would call the method like the following, LINQ would error out.

emailNotifications = EmailNotification.GetEmailNotifications(
  new EmailNotificationQuery() { EmailNotificationTemplateIDs = new List<int> 
      {(int)HalTypes.EmailNotificationType.EmployeeAddedToTask, 
       (int)HalTypes.EmailNotificationType.EmployeeRemovedFromTask} }); 

If I DID NOT define the list inline, then the query would work, but when it was defined inline, it would puke the following error:

Method 'Boolean In[Int32](Int32, System.Collections.Generic.IEnumerable`1[System.Int32])' has no supported translation to SQL.

Is this a LINQ bug, or am I missing somthing? Thanks in advance for any help.

Answer 1

The error message is giving you the information you need.

Method 'Boolean In[Int32](Int32, System.Collections.Generic.IEnumerable`1[System.Int32])' has no supported translation to SQL.

LINQ is trying to create a SQL statement to match the instructions you've provided, but is choking on the In method, which I'm assuming is an extension method you've defined on

Nullable<int>

Try instead:

emailNotifications = emailNotifications.Where(en => query.EmailNotificationTemplateIDs.Contains(en.EmailNotificationTemplateID));

EDIT: If you're not using it already, LinqPad is a great way to debug your LINQ queries. Not affiliated with it at all, just a happy user.