KyloRen KyloRen - 3 months ago 16
C# Question

How to update Database with LINQ? Linq-to-SQL

I am trying to update a the database via Linq-to-SQL.

I have a table named

Staff_Time_TBL
which has 9 columns. I am trying to update a single columns record on potentially multiple rows.

My LINQ query is as follows. It grabs data from column
Section_Data
from a specified date to another specified date and from a single employees Number.

DatabaseDataContext Sql = new DatabaseDataContext(ConnectionString);

var getList = Sql.Staff_Time_TBLs.Where(
staff => staff.Staff_No == staffNo &&
staff.Date_Data >= dateFrom &&
staff.Date_Data <= dateTo)
.Select(staff => staff.Section_Data).ToList();


I am then going to use a
foreach
loop to iterate though the collection and update the database,

foreach (var item in getList)
{
item.Section_Data = "myValue";
}
Sql.SubmitChanges();


The problem is that this below , the entity
Section_Data
does not exist.

item.Section_Data


So, I am thinking that I have not put together the LINQ correctly to be able access the entities? How do I amend the query so as I can access the entity to update the database?

Answer
var getList = Sql.Staff_Time_TBLs.Where(
               staff => staff.Staff_No == staffNo &&
               staff.Date_Data >= dateFrom &&
               staff.Date_Data <= dateTo)
               .Select(staff => staff.Section_Data).ToList();

This query will give you only data of type Section_Data because you are selecting Section_Data, and when you run a for each on that, you will be able to access the attributes related to that only, hence just remove the select part from the query.

var getList = Sql.Staff_Time_TBLs.Where(
               staff => staff.Staff_No == staffNo &&
               staff.Date_Data >= dateFrom &&
               staff.Date_Data <= dateTo)
               .ToList();

Now you will be able to access the attributes inside it.

foreach (var item in getList)
{                    
   item.Section_Data = "myValue";
}
Sql.SubmitChanges();