update a property value during linq to sql select (involves join)

Question

Ok I have seen many questions that based on their text could be something like this but not quite. Say I have something like this

(from r in reports
                      join u in SECSqlClient.DataContext.GetTable<UserEntity>()
                      on r.StateUpdateReportUserID equals u.lngUserID
                      select r).

If reports have a bunch of say reportDTO class and I want to select from a list of that DTO but at the same time set one property to a property in userEntity how would I do that? Basically I want all other fields on the report maintained but set a user name from the user table. (There is a reason this is not done in one big query that gets a list of reports)

What I am looking for is something like Select r).Something(SOME LAMBDA TO SET ONE FIELD TO userEntity property).

LINQ is great at querying data (that's what the Q is for after all). It's not great at updating data. — SWeko, Aug 06 '13 at 14:25
I am not trying to update data. I am trying to update the object. For a reason that is a long story the users are if a different context as the reports and I need to populate the one field in the resultant DTO. I would rather not have to re-select all the fields into a new list of DTOs. — Nick Birke, Aug 06 '13 at 14:36

score 3 · Accepted Answer · edited May 23 '17 at 12:05

There is a dirty way to do this, which is

var repQuery = from r in reports ... select new { r, u };
var reps = repQuery.Select(x => { x.r.Property1 = x.u.Property1; return x.r; };

However, When it comes to functional programming (which Linq is, arguably) I like to adhere to its principles, one of which to prevent side effects in functions. A side effect is a change in state outside the function body, in this case the property value.

On the other hand, this is a valid requirement, so I would either use the ForEach method after converting the query to list (ToList()). Foreach is expected to incur side effects. Or I would write a clearly named extension method on IEnumerable<T> (e.g. DoForAll) that does the same, but in a deferred way. See Why there is no ForEach extension method on IEnumerable?.

I don't think this example works because in the the second select, considering my code example- u is no longer available. — Nick Birke, Aug 06 '13 at 14:52
Keep in mind I am trying to do something like r.Property = u.SomeProperty — Nick Birke, Aug 06 '13 at 14:52

update a property value during linq to sql select (involves join)

1 Answers1