Query and Assignment in a Single Step

Question

I have a class that has 3 properties:

class Three
{
   int ID { get; set ;}
   string Name { get; set; }
   double Value { get; set; }
}

I then have a linq query that returns a Queryable<Three> but only ID and Name are defined within the table.

var three = from t in db.Threes
            select t;

three = { {ID=1, Name="A", Value=0},{ID=2, Name="B", Value=0},...}

So Value is not defined within the db.Threes table so it defaults to 0 as doubles do. I then iterate over each item in var three to set Value based upon another table:

for (int i = 0; i < three.Count(); i++)
{
   three[i].Value = (from v in db.Values
                     where v.ThreeID = i.ID
                     select v.Value).First();
}

return three;

The Queryable<Three> three now contains definitions for .Value:

three = { {ID=1, Name="A", Value=99},{ID=2, Name="B", Value=100},...}

The key thing that I'm trying to do is to still return a Queryable<Three>, but to also set the Value property.

Is there a better way to do this instead of using a for loop?

I'd like to be able to extend this to any class with a Value property that is assigned from another table; suppose I have a class Four:

class Four
{
   int ID {get; set;}
   string Name {get; set;}
   DateTime Date {get; set;}
   double Value {get; set;}
}

The first query would populate with ID, Name and Date and I still want to be able to assign Value. I feel like there is a better way to do this with linq that I just can't come up with.

Edit: suppose I'm calling db.GetTable<T> and Three and Four are really generic arguments that implement a .Value interface. I can't explicitly instantiate a generic type because I would have to hard code the properties.

Answer 1

I think you are looking for join. You are assigning values from another table whose id is the same as the Three table.

You can build back a Three object like this:

var three = from t in db.Threes
            join v in db.Values on t.ID equals v.ThreeID
            select new Three{
                            ID = t.ID, 
                            Name = t.Name,
                            Value = v.Value 
                         };

What you were doing before by looping, that is a performance hit. What happens is that you called to the database to loop it, then for each loop, you make another call back to the database. If your tables are linked by those Ids, then using this code makes 1 single database call and no in memory looping.