if exists statement in sql to linq

Question

What is the linq equivalent of the following statement ?

IF NOT EXISTS(SELECT UserName FROM Users WHERE UserName='michael')
BEGIN
INSERT INTO Users (UserName) values ('michael');
END

also can you suggest any sql-to-linq converters? I am currently using LINQPad which does a great job in terms of writing linq code where you can also see the generated sql code however when I click the little linq sign, nothing is displayed.

Answer 1

It can't be done in LINQ2SQL with a single statement as the LINQ syntax and extension methods don't support inserts. The following (assuming a datacontext named db) should do the trick.

 if (!db.Users.Any( u => u.UserName == "michael" ))
 {
      db.Users.InsertOnSubmit( new User { UserName = "michael" } );
      db.SubmitChanges();
 }

Answer 2

Extension method that implements tvanfosson's solution:

  /// <summary>
  /// Method that provides the T-SQL EXISTS call for any IQueryable (thus extending Linq).
  /// </summary>
  /// <remarks>Returns whether or not the predicate conditions exists at least one time.</remarks>
  public static bool Exists<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
  {
     return source.Where(predicate).Any();
  }

  /// <summary>
  /// Method that provides the T-SQL EXISTS call for any IQueryable (thus extending Linq).
  /// </summary>
  /// <remarks>Returns whether or not the predicate conditions exists at least one time.</remarks>
  public static bool Exists<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, int, bool>> predicate)
  {
     return source.Where(predicate).Any();
  }

The extension method would then be used:

  bool exists = dataContext.Widgets.Exists(a => a.Name == "Premier Widget");

Although the .Where().Any() combination works sufficiently, it does certainly help the logic flow of the code presentation.

Answer 3

I like that one

if (!db.Users.Any( u => u.UserName == "michael" ))
{
    db.Users.InsertOnSubmit( new User { UserName = "michael" } );
    db.SubmitChanges();
}

Answer 4

Michael, where do you put the Exists code? In a DataContext class?

Answer 5

Put the Exists code in a static class. e.g. add a class to your project w/ something like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;

namespace company.project
{
   static class LinqExtensions
    {
        /// <summary>
        /// Method that provides the T-SQL EXISTS call for any IQueryable (thus extending Linq).
        /// </summary>
        /// <remarks>Returns whether or not the predicate conditions exists at least one time.</remarks>
        public static bool Exists<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
        {
            return source.Where(predicate).Any();
        }

        /// <summary>
        /// Method that provides the T-SQL EXISTS call for any IQueryable (thus extending Linq).
        /// </summary>
        /// <remarks>Returns whether or not the predicate conditions exists at least one time.</remarks>
        public static bool Exists<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, int, bool>> predicate)
        {
            return source.Where(predicate).Any();
        }


    }
}