CODEWITHSUNDEEP
asp.netlinq-to-sql
alexm
alexm

Reputation: 530

LINQ-TO-SQL Query optimization 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();

        }
    }

Upvotes: 2

Views: 207

Answers (1)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28698

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.

Upvotes: 2

Related Questions