score:3
you can do these things to help the generated query.
- return only what you need. now you are returning everything but you only ever use timestamp so just return that. you are setting statuscode below however you are already filtering this in the where clause so you know that all returned items have a
statuscode
of "xx" so no need to retrieve that too. this is less data coming back across the network and less cycles taken to map that data to objects and less memory that the data takes up. - you should look at the generated query by ef. you can do this using a sql profiler tool (sql server has one called sql profiler). then look at the query plan and see if there is anything that could benifit you like adding a missing index. this profiling should be done on the database server, not in c#.
- consolidate your where clauses because it's easier to read.
code
// list of timestamps from database
var timestamps = systemdb.devicestates.where(s => s.device_id == deviceid &&
s.timestamp > startdate &&
s.timestamp < enddate &&
s.statuscode == "xx")
.select(x => x.timestamp).tolist();
if you still wanted your status code because you removed the filter you could do it like this
var timestamps = systemdb.devicestates.where(s => s.device_id == deviceid &&
s.timestamp > startdate &&
s.timestamp < enddate &&
s.statuscode == "xx")
.select(x => new {x.timestamp, x.statuscode})
.tolist();
score:1
you can do thin one where clause
list<devicestate> liststate = systemdb.devicestates.where(
l => l.device_id == deviceid
&& l.timestamp > startdate
&& l.timestamp < enddate
&& l.statuscode == "xx"
).tolist();
then use for
rather than foreach
, because foreach
slower in case of big amount data
for (int i = 0; i< liststate.count; i++ )
{
object.statuscode= liststate[i].statuscode;
object.timestamp = liststate[i].timestamp;
}
score:1
igor's answer already shows the most obvious improvements that you should do in any case.
now, if you handly lots of data, then you may want to do it in multiple threads parallel with system.threading.tasks.parallel.foreach. code (with other improvements):
var devicestatetime = systemdb.devicestates.where(s => s.device_id == deviceid
&& s.timestamp > startdate)
&& s.timestamp < enddate
&& s.statuscode == "xx");
parallel.foreach(devicestatetime, (time) =>
{
object.statuscode = "xx";
object.timestamp = time;
});
please note, that i did not test it (i wrote it down from memory), so i may have mistyped somewhere.
Source: stackoverflow.com
Related Query
- Linq query and Foreach on large number of records from SQL database
- How can I check the number of calls to the database in LINQ query when using .NET Core and Code First?
- Get min and max value from database Linq to Sql query in MVC?
- how to fetch data from database using linq query for relationship 1:N and N:N (between 3 entity) in asp.net mvc EF code first?
- Delete all records from a database using LINQ to SQL
- Retrieve and print data from dynamic sql query with linq
- Is a full list returned first and then filtered when using linq to sql to filter data from a database or just the filtered list?
- LINQ Query To Join Two Tables and Select Most Recent Records from Table B corresponding to Table A
- Pull data from multiple tables in one SQL query using LINQ and Entity Framework (Core)
- How to return list of items from linq to sql query and call it in a console application
- LINQ query translate from SQL which uses subquery containing GROUP BY and COUNT
- SQL Query or Linq query to get No of Events per day from database
- SQL query shows good database values, but LINQ to Entity framework brings a null value from nowhere
- Nullable DateTime from SQL database into Datatable using generics and Linq
- Using SQL instead of Linq to query database and return Json
- Get Records from table where quantity is not between fromquantity and ToQuantity using Linq to SQL
- How to simplify this LINQ to Entities Query to make a less horrible SQL statement from it? (contains Distinct,GroupBy and Count)
- I get a query string as return value from LINQ to SQL and WCF Services and no concrete value
- Get data from database using linq query and display it in dynamically generated labels in repeater
- how to write Like Query for search Username from database in LINQ and ADO.NET
- How do I write a linq to sql query to find records where field name can match one of dynamic number of strings?
- Linq to sql - Optimal way to return sub-set of records AND the total number of records
- How can I simplify (speed up) the selecting query from a database that contains more than 1 million records using LINQ
- Is there a possibility to take 100 records then other 100 record from database table using LinQ and Take()
- How to populate a DropDownList from a SQL database using linq and only select Distinct Values
- Linq Query Return Duplicate Record From Sql Database
- Getting number of records by date in LINQ to SQL query even if no results
- How to avoid reading same records from SQL Database from multiple threads in LINQ to Entities
- Linq Sql Query result is different from the SQL query run in the database
- Using Linq to query objects from a database and checking if they exit in a another list or array
More Query from same tag
- Func parameter in LINQ expression
- Using LINQ to XML to traverse an HTML table
- How to use linq functions like Sum() with dynamic variable
- Updating Database with Linq to Entities
- How do I add an xml file to my project?
- "Shear" sort list of jobs for multiple threads efficiently
- Escaping the escape character does not work – SQL LIKE Operator
- Dynamic LINQ Query in SharePoint
- how add the list of strings into string using linq?
- Select distinct from dataTable.AsEnumerable
- c# extension method with linq
- LINQ side effects
- SortedSet<string> Linq .Except with Comparer strangeness
- Linq not adding data to table, using InsertOnSubmit() & SubmitChanges()
- Creating multiple results from Linq query
- Entity Framework 6 context.Database.SqlQuery<bool>
- Using LINQ to find updated rows in DataTable
- Entity Framework - Convert an SQL query to Entity Framework Lambda expression
- Linq to DataTable (GROUP BY) when columns are not known
- How to user where in Set<> Method in Entity Framework
- use left join in LINQ method synthax
- Filtering in a group using Linq and EF and SQL Server
- Including Select Count(*) in Linq Query VB.NET
- Referencing the collection your working on within LINQ
- Search for overlapping date ranges
- I'd like to make a Linq to object (Basic, Dictionary)
- NullReferenceException in LINQ to XML query
- Adding Sum per row in PIVOT using LINQ
- XElement add node on specific position not working as expected
- Does Linq have a startup penalty?