If Not Exists

Is there a better way?

internalstaticvoid CreateTag(String tag)

{

// instance the data context
using (DiamondDataContext dc =newDiamondDataContext())
{
// construct query
var resultQuery =from tin dc.Tags
where t.Name.Equals(tag)
select t;
// if this tag exists already, move on.
if (resultQuery.Count<Tag>() == 0)
{
// create the new tag
Tag newTag =newTag();
// assign the value
newTag.Name = tag;
// add it to the collection
dc.Tags.Add(newTag);
// save it
dc.SubmitChanges();
}
}

}

[2482 byte] By [paul-d-murphy] at [2008-1-8]

«« Not displaying the key datacolumn in a datagrid
»» numeric up down problem

# 1

Paul, what would you query ideally look like if you were to write it in SQL?

JoeAlbahari at 2007-10-2 >

top of Msdn Tech,Visual Studio Orcas,LINQ Project General...

# 2

Actually, you bring up an interesting example that should be addressed.

This kind of "test and set" needs to be an atomic operation from your code all the way to the db and back. Some other user (or code) could Add a tag between the time you test it and the time you set it. This may not be an issue in your environment if this is an app-specific db. However it could be a big issue (or unseen bug) in a multi-user environment. Short story, I think we need a atomic test-and-set api that handles this kind of thing correctly.

WilliamStacey at 2007-10-2 >

# 3

Joe Albahari wrote:

Paul, what would you query ideally look like if you were to write it in SQL?

IF NOT EXISTS(SELECT TagName FROM diamond_Tags WHERE TagName = @name)

INSERT INTO diamond_Tags(Tagname) VALUES( @name)

paul-d-murphy at 2007-10-2 >

# 4

You could do something like this:

Tag newTag = (from tags in dc.Tags
where tags.Name == tag
select tags).ToList().DefaultIfEmpty(

new Tag() { Name = tag }).SingleOrDefault();

dc.Tags.Add(newTag);
dc.SubmitChanges();

This would be better if you could bypass the ToList() and just use DefaultIfEmpty passing in the default value but you will get an exception that override is not supported.

I know this is a little ugly but it should work

Hope this helps

NickVanMatre at 2007-10-2 >

# 5

I was having a similar issue- I built a little extension method to wrap the work up and make it all tidy (this also has the added benefit of being directly assignable within other adds on the same context):

Code Snippet

public static class FetchOrCreateExtension
{
public static T FetchOrCreate<T>(this Table<T> table, Expression<Func<T, bool>> where, T newValue) where T:class
{
T existing = table.SingleOrDefault(where);

if (existing != null)
return existing;

// clone the DataContext
Type dataContextType = table.Context.GetType();
string ctxConStr = table.Context.Connection.ConnectionString;
using (DataContext newDC = (DataContext)Activator.CreateInstance(dataContextType, ctxConStr))
{
Table<T> writableTable = newDC.GetTable<T>();
writableTable.Add(newValue);

newDC.SubmitChanges();
}
return table.Single(where); // fetch on the existing context so the caching behavior is consistent
}
}

Obviously there are a number of ways to shoot yourself in the foot with this approach. It's not atomic, so it'd be better if it handled duplicate key exceptions by re-running the original predicate query. There's no enforcement that the predicate actually matches the key you pass- be careful, and make sure you can't get more than one value!

This works for what I need right now...

MattDavis at 2007-10-2 >

# 6

Thanks guys for the assist. I'll try both strategies and then just a simple SPROC and see what gives the best mix of performance and ease of use.

Paul

paul-d-murphy at 2007-10-2 >

Visual Studio Orcas

LINQ Project General

Visual Studio Orcas Hot Topic

Visual Studio Orcas New Topic

Visual Studio Orcas

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

If Not Exists document under LINQ Project General classified...

If Not Exists

Visual Studio Orcas Hot Topic

Visual Studio Orcas New Topic

Visual Studio Orcas

Site Classified