Josh-CO Dev

Solving the worlds problems one line of code at a time.

LINQ in the Business Database World

Leave a comment


I have come across two dilemmas lately in my professional career, the first of which is that I love LINQ. The second dilemma is that the way we do business does not provide us with much of a need for it. Really, these two aren’t dilemmas on their own, but combine the two and the problems begin to arise. All of our apps are generally just a front end to a database. We fetch data via a dataset and use this for all databinding and pass all CRUD operations through a webservice to call stored procs. With this type of a model, LINQ just isn’t that efficient unless you want to pull all of your data at once and then parse through your collection, which can be cumbersome on the initial load for a large table.

There is one place where I have found good use for LINQ, in batch jobs where I am pulling data from one source and posting it to another, particularly if I need to scour the data. Below is some LINQ code that I have used to take a dataset that I have pulled in from a source database, scour out any rows that have a null or <0 value, then return the results in a datatable. I tried this many other ways and LINQ was by far the fastest. This operation will run on over 100,000 records in mere seconds.

 

private DataTable scourDatasetLINQ(DataSet ds)

{

try

{

var results = from myRow in ds.Tables[“PIData”].AsEnumerable()

where myRow.Field<double?>(“value”) != null && myRow.Field<double?>(“value”) >= 0

select myRow;

 

DataTable dt = results.CopyToDataTable<DataRow>();

return dt;

}

catch (Exception ex)

{

LogExceptionToFile(“CEMSBatch”, ex, 30);

return null;

}

}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s