c# LINQ to SQL and proper procedure

Hi

I would like please a LINQ to SQL expert to comment. I am using LINQ to SQL but it appears to me that itis not an effecive way to communicate to the database, but perhaps it is the way that I am doing it.

Firstly I have a datacontext class as follows

  #region DataContext
    public class TQ : DataContext
    {
        public static string ConnectionString = "Password=74XXX;Persist Security Info=True;User ID=questuser;Initial Catalog=Tradequest; Data Source=LIVESERVER";
        private static readonly string connString = ConnectionString;
        public TQ()
            : base(connString)
        { }
    }
    #endregion

then in each method I opent that context as per the following small method example (I hve defined the class seperately)

     public static int GetUniqueId(int marketId, int selectionId)
          {
              int rt = 0;
              using (TQ contextRO = new TQ())
              {                  
                  Table<clsRunnersWithTrades> tblRunnersWithTrades = contextRO.GetTable<clsRunnersWithTrades>();
                  try
                  {
                      var updateRunner = (from r in tblRunnersWithTrades where r.Marketid == marketId && r.Selectionid == selectionId select r).FirstOrDefault();
                      if (updateRunner != null)
                          rt = updateRunner.id;
                  }
                  catch (Exception ex) { LogAuditRecord("GE", "General error User functions : " + ex.ToString()); }
                  return rt;
              }

          }


Where thi all seem to struggle for me is

(a) at the point where the execution is triggered (deferred or non) the table command has it load all the table records. If I look at the table line, if there is 100,000 records in the table it will load all 100000 records, with the var command then pulling a subset from this. That does not seem efficient when I only want one of several recordsin the dataset?

(b) I have constantly battled concurrency errors when using threading, such that I have had to put an (UpdateCheck = UpdateCheck.Never) command into the definong of the class.

Surely there is something I am doing that is not quite right, and guidance or ideas would be appreciated. If tht is ditch LINQ and drop back to datasets and sprocs then happy to recieve that advice also

Thanks
z488758Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anurag ThakurTechnical ManagerCommented:
Need to understand what are you trying to achieve then only can we suggest a solution
0
z488758Author Commented:
Thanks.

All I want is to efficienetly communicate to the database. I have an application that is adding to or interogating the database. Simple requirement, about 10 tables, no associations between tables, maybe 100,000 records in 1 or 2 tables, only in the thousands in the others. I simply want to add records or update values in the master file table, as quickly as possible

0
Kelvin McDanielSr. Developer/ConsultantCommented:
Linq is always deferred until needed. You're right that it's going to grab everything, then take what it needs. Nothing is faster than straight ADO.NET when you want to talk to a database.

Also, if everything is forward-only and take once/write once for distribution to other tables then I wouldn't even mess with data sets... I would just use data readers. If each row has potentially more than one target the I would use data tables/datasets.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Fernando SotoRetiredCommented:
Hi z488758;

The following line in your code will load the complete table from the database to your local system. Unless you need to do the processing on your system do not do this.

Table<clsRunnersWithTrades> tblRunnersWithTrades = contextRO.GetTable<clsRunnersWithTrades>();

To your question, "That does not seem efficient when I only want one of several recordsin the dataset?", you are correct the above line should be removed. See snippet below:

public static int GetUniqueId(int marketId, int selectionId)
{
    int rt = 0;
    using (TQ contextRO = new TQ())
    {                  
        try
        {
            var updateRunner = (from r in contextRO.clsRunnersWithTrades 
                                where r.Marketid == marketId && r.Selectionid == selectionId 
                                select r).FirstOrDefault();
                                
            if (updateRunner != null)
                rt = updateRunner.id;
        }
        catch (Exception ex) 
        { 
            LogAuditRecord("GE", "General error User functions : " + ex.ToString());
        }
        
        return rt;
    }
}

Open in new window


To your question marked as (b), how are you using the DataContect one for multiple threads? more info would be needed.

Fernando
0
z488758Author Commented:
Fetnando

Thanks for the comment on not seperately initialising the table, that is valuable and I will adopt thanks. As far as the datacontext, as I am opening within each method, then each thread will open once of in some cases multiple times. I have not attempted to hold a datacontext open, thinking that that would only exacerbate the table problem, as the using statement was atleast disposing of the table. Given the above change you have suggested, should I use one datacontext, or one context per thread and if so if you could point me in the right direction as the best way to make available across multiple threads would be appreciated

Thanks very much for your help

Ian
0
Fernando SotoRetiredCommented:
Hi Ian;

This article on DataContext Lifecycle should answer you question. Rick Strahl has done a great job and more then I can do here.

Linq to SQL DataContext Lifetime Management
http://www.west-wind.com/weblog/posts/246222.aspx

Fernando
0
z488758Author Commented:
Fernando

Thanks for your comments. In your suggestion not to initialise the table but call it directly in the linq command, I can't see the table in the command. That is,

 contextRO.clsRunnersWithTrades

does not see the "clsRunnersWithTrades". Can you think of something in the initialisation of the context class or similar that would stop the class from being called from the contactas you have suggested?

Thanks

Ian
0
Fernando SotoRetiredCommented:
Hi z488758;

In your original code you had this line of code.

Table<clsRunnersWithTrades> tblRunnersWithTrades = contextRO.GetTable<clsRunnersWithTrades>();

Open in new window


Which creates a table of type clsRunnersWithTrades in the model that maps to a table in the store / database by the same name if you did not change it in the DBML file / model. So removing that line of code and just using the query to fill with only the filtered records you only need the following:

var updateRunner = (from r in contextRO.clsRunnersWithTrades 
                    where r.Marketid == marketId && r.Selectionid == selectionId 
                    select r).FirstOrDefault();

Open in new window


Should be accessing the same table. Now if you have your IDE set up to do pluralization of name then a table in the database lets say Customers would become Customer in the Model. So this name clsRunnersWithTrades could be clsRunnersWithTrade in the model.

If you are still having issues with this please post the code of the DBML file here.

Fernando
0
z488758Author Commented:
Thanks Fernando.

 Intellisense when typing   (from r in contextRO.  does not see any classes.

I did not use the designed to create the mapping but did it manually as per the attached.

Your help is very much appreciated

Ian
[Table(Name = "tblRunnersWithTrades")]
    public class clsRunnersWithTrades
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "int Not Null IDENTITY")] public int id;
        [Column] public int Marketid;
        [Column] public int Selectionid;

Open in new window

0
Fernando SotoRetiredCommented:
Hi Ian;

I suspect that you are missing something in your DataContext class, a property as shown in the code snippet below.

public class TQ : DataContext
{
    public static string ConnectionString = "Password=74XXX;Persist Security Info=True;User ID=questuser;Initial Catalog=Tradequest; Data Source=LIVESERVER";
    private static readonly string connString = ConnectionString;
    
    public TQ() : base(connString)
    { }
    
    public System.Data.Linq.Table<tblRunnersWithTrades> tblRunnersWithTrade
    {
        get
        {
            return this.GetTable<tblRunnersWithTrades>( );
        }
    }    
}

Open in new window


Fernando
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
z488758Author Commented:
Thanksyou Fernando, you are spot on, that has fixed it, thankyou for your first class support and advice

Ian
0
Fernando SotoRetiredCommented:
Not a problem Ian; glad I was able to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.