How to represent an sql exists() command in LINQ

Question

I have a SQL problem that I use over and over again, but now that I need to do stuff more in LINQ. How do I do this? Is there a site that converts your sql to linq?

conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
//query
command.Parameters.Add(new SqlParameter("@email", email));

//else
command.CommandText = "if exists(SELECT pk_email FROM MyTable WHERE pk_email = @email) begin " +
                "UPDATE MyTable SET last_login = getdate(), is_logged_in = 'true' WHERE pk_email = @email; " +
                "end else begin " +
                "INSERT INTO MyTable (pk_email, last_login, is_logged_in) VALUES (@email , getdate(), 'true'); " +
                "end";
command.ExecuteNonQuery();

msarchet · Accepted Answer · 2010-10-20 01:28:30Z

1

You could do something like this

if(From emails In MyTable Where emails.pk_email == email).Any) {
    'Then update your data here
}
else {
     'Insert your data
}

If you need help with the inserts or the updates on the datacontext just drop a comment.

answered Oct 20, 2010 at 1:28

msarchet

15.3k2 gold badges45 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kramer Over a year ago

Now does this hit the database more than what I wrote? I know there is more than one way to do thing. It looks cleaner then using a sqlcommand, but which is best?

Yrlec · Accepted Answer · 2010-10-20 13:47:59Z

0

In Entity Framework I usually use the following extension-method to achieve what you want (see the comment for an example).

/// <summary>
    /// Updates and entity and save it to the database.
    /// If it doesn't exist it creates a new entity and saves it to the database.
    /// <example>
    ///     <code>
    ///         //Updates or inserts a row into Account. The inserted/updated row will have its AccountNumber set to "17".
    ///         var account = db.Accounts.InsertOrUpdate(a => a.ID == id, a => a.AccountNumber = "17");
    ///     </code>
    /// </example>
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    /// <param name="allEntities"></param>
    /// <param name="entityFilter"></param>
    /// <param name="propertySetter"></param>
    /// <returns></returns>
    public static TEntity InsertOrUpdate<TEntity>(this ObjectSet<TEntity> allEntities, Func<TEntity, bool> entityFilter,
                                                  Action<TEntity> propertySetter) where TEntity : class, new()
    {
        //First we use the entityValueMapper to search for an existing entity.
        var entity = allEntities.Where(entityFilter).FirstOrDefault();
        if (entity == null)
        {
            entity = new TEntity();
            allEntities.AddObject(entity);
        }
        propertySetter(entity);
        allEntities.Context.SaveChanges();
        return entity;
    }

answered Oct 20, 2010 at 13:47

Yrlec

3,4586 gold badges42 silver badges76 bronze badges

2 Comments

Kramer Over a year ago

This looks good but i'm too of a novice to understand it. I come from a classic asp background and when variables and datatype can have extras "<>" I get lost. Reading up on it now, though.

Yrlec Over a year ago

Yeah, it's a bit abstact because it's created to work with any Entity Framwork-entity you wish to use. Once you get a hang of generics and high-order functions you'll see that's pretty straightforward :).

Kramer · Accepted Answer · 2010-10-20 13:43:45Z

Something like this? So how many hit to the database does this cause? And by using linq does it protect me more for sql injection?

App_DAL.DataDataContext h = new App_DAL.DataDataContext();
if ((from emails in MyTables where emails.pk_email == email select emails.pk_email).Any()) {
    //Then update your data here
    var messenger = (from emails in MyTables where emails.pk_email == email select emails).Single();
    messenger.last_login = DateTime.Now;
    messenger.is_logged_in = true;
    h.SubmitChanges();
}
else 
{
    //Insert your data
    App_DAL.MyTable msg = new App_DAL.MyTable();
    msg.pk_email = email;
    msg.is_logged_in = true;
    msg.last_login = DateTime.Now;
    h.MyTables.InsertOnSubmit(msg);
    h.SubmitChanges();

}

Collectives™ on Stack Overflow

How to represent an sql exists() command in LINQ

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related