LINQ-TO-SQL Query optimization question

Question

I have a join table of Person's Sports with the following schema:

PersonId
SportID

How do I effectively set Person's sports in one transaction i.e.

void SetPersonsSports(List<PersonsSportsL> sports);

My way of doing it was deleteing all person's sports with x.PersonId = PersonId first and then adding all sports back in but I know that delete operation is expensive and wanted to see what other people are doing when faced with a similar task.

Thank you.

UPDATE:

Here's what I was thinking....

    void SetPersonsSports(List<PersonsSports> PersonSports) 
    {

        using (DataContext dc = conn.GetContext())
        {
            if (PersonSports.Select(x=>x.PersonID).Distinct().Count()>1)
                throw new Exception("This method can only be used with a set of sports for the same person ID at a time");
            var sportIDs = PersonSports.Select(x=>x.SportID);
            bool submitFlag = false;                
            var toRemove = dc.PersonSports.Where(x=>!sportIDs.Contains(x.SportID));
            if (toRemove.Count()>0)
            {
                 dc.PersonSports.DeleteAllOnSubmit(toRemove);
                 submitFlag = true;
            }
            var toAdd = dc.PersonSports.Where(x=>!sportIDs.Contains(x.SportID));
            if (toAdd.Count()>0)
            {
                 dc.PersonSports.InsertAllOnSubmit(toAdd);
                 submitFlag = true;
            }
            if (submitFlag)
                 dc.SubmitChanges();

        }
    }

Kirk Broadhurst · Accepted Answer · 2011-06-20 05:49:54Z

2

Your suggestion is completely correct - there's not really a better way to do this.

You have a small mistake / typo

var toAdd = dc.PersonSports.Where(x=>!sportIDs.Contains(x.SportID));

which should be (and you should use camel case for method parameters)

var toAdd = PersonSports.Where(x=>!sportIDs.Contains(x.SportID));

The only suggestion I'd make is to consider whether you need to determine which records to remove & delete before you do any database inserts and updates. You may run into problems if your method is more complex than this because the collection may change before you enumerate. I prefer to ensure my collections are evaluated immediately.

// just get the sport ids from the database here 
// rather than the entire PersonSport objects
var toRemove = dc.PersonSports.Where(x=>!sportIDs.Contains(x.SportID)).ToList();
var toAdd = PersonSports.Where(x=>!sportIDs.Contains(x.SportID)).ToList(); 

if (toRemove.Any())
{
    dc.PersonSports.DeleteAllOnSubmit(toRemove);                        
}               

if (toAdd.Any())               
{                    
    dc.PersonSports.InsertAllOnSubmit(toAdd);
}

dc.SubmitChanges();

Finally, note that this will break if you have more than 2100 sports because the ids will be sent as parameters, and that is a hard SQL limit on the maximum number of parameters. If this is an issue you can count how many parameters you are working with and execute them 2000 at a time.

answered Jun 20, 2011 at 5:49

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

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

2 Comments

alexm Over a year ago

Thank you so much for finalizing my thought process and providing a complete solution with extremely important remarks especially about 2100 parameter limit. Thank you again. And... I never used Any()... it is quite a handt replacement for Count()>0 +1

Kirk Broadhurst Over a year ago

@alexm You are very welcome! The Any operator is particularly suited to this type of task as it's more efficient. For example, if you have 10,000 items in a collection the Count actually has to count all the items, but the Any operator simply finds one and stops looking.

Collectives™ on Stack Overflow

LINQ-TO-SQL Query optimization question

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related