A beginners Entity Framework query to populate a WinForms ListView.

Question

I've just started learning how to use the Entity Framework to write a very simple C# network monitoring program - this is a learning exercise to try and "drive home" what I've only read about to date. I'm also new to C# and LINQ (just to complicate things further.)

I believe I have the data model suitably normalised but I may be wrong. Visual Studio generates a conceptual model that looks OK. I've pluralised the associations and EntitySets where necessary, but I'm struggling to perform what I think is a fairly basic query/projection on the data.

The database contains 3 tables:

[Server]    - A server defined by the user that should be pinged.
ServerID    - primary key
HostAddress - IP or hostname

[Result]    - A result containing data about the last server test
ResultID    - primary key
ServerID    - foreign key on [Server].[ServerID]
StateID     - an integer used to lookup one of 3 possible Server states
TimeStamp   - Time stamp of last ping

[State]     - A lookup table containing an integer -> string mapping.
StateID     - a unique key
StateLabel  - human-readable string like "unreachable" or "OK" or "timeout"

I have manually populated the database using a few simple entries - just enough to give me something to work with.

For starters, I would like to present all of the Result data in a ListView on a WinForm. The ListView contains the following static columns:

State | Server Address | Last checked

In theory, the ListView's data needs to be generated by projecting(?) across each of the 3 tables:

• The "State" column should display the human-readable [State].[StateLabel] linked from [Result].[StateID]
• The "Server Address" column should display [Server].[HostAddress] linked from [Result].[ServerID]
• The "Last Checked" column should display [Result].[TimeStamp]

Since I have no need for the object materialisation and/or change-tracking features of ObjectServices, am I correct in thinking it would be more efficient/correct to use Entity SQL/EntityClient and DbDataReader? If so, what would a suitable Entity SQL query look like?

For what it's worth, I tried using LINQ to Entities and anonymous types in a method but was thwarted by a lack of understanding on a suitable return type:

var results = from r in _context.Result
select new
{
    State = (from s in _context.State
         where s.StateId == r.StateId
         select s.StateLabel),
    r.ServerReference.Value.HostAddress,
    r.TimeStamp
};

return results.ToList(); // <- No can do.

Thanks for your help!

Steve

Answer 1

Well you won't be able to return a list of anonymous types unless you cast them to object and have the signature define the return type as List<object> (or suitable interface). Your other issue is that the subquery for State will actually return an IQueryable instead of a single entry (you can use the First extension method with EF to get the first matching item.) Although if you have the foreign key relationship the model should have setup an navigation property for the state as well and you should be able to use the property attached instead of a subquery. So if you would like to have this as a method call that returns a list of objects you will have to create a type that represents the transform or downcast to object. Otherwise you could do it at the form level (this all depends on your needs) where you are attempting to bind the list.

public List<object> GetStuff()
{
    var results = from r in _context.Result
    select new
    {
        State = r.StateNavigationProperty.StateLabel, //If FK
        State = _context.State.First(state => state.StateId == r.StateId), //If Not FK
        HostAddress = r.ServerReference.Value.HostAddress,
        TimeStamp = r.TimeStamp
    };

    return results.Cast<object>().ToList();
}

...

myListView.DataSource = GetStuff();

And like I said the other alternative is to either create a class for the transform or bind the list directly to the query.

public class SimpleStuff
{
    public string State { get; set; }
    public string HostAddress { get; set; }
    public DateTime TimeStamp { get; set; }
}

Then just add the class to the select new ala select new SimpleStuff and change the method signature to reflect the class and remove the cast in the return.