asked on

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

Anurag Thakur

Need to understand what are you trying to achieve then only can we suggest a solution

z488758

ASKER

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

Kelvin McDaniel

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.

Fernando Soto

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

z488758

ASKER

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

Fernando Soto

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

z488758

ASKER

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

Fernando Soto

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

z488758

ASKER

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

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

z488758

ASKER

Thanksyou Fernando, you are spot on, that has fixed it, thankyou for your first class support and advice

Ian

Fernando Soto

Not a problem Ian; glad I was able to help.