DeeTee DeeTee - 3 months ago 7
C# Question

C# Controller method entity model

I have a controller method that uses an edmx model in my asp.net mvc project that is used to get data from a View in a SQL database. Currently, I'm only interested in two columns from that table. I'm able to acquire the data using my entities class and a simple Linq statement to store the returned data to a list. What I'd like to do is iterate through the list, and check where those two columns are both populated with data (the value can be either null or populated with data) and return the row that gives me a true. Below is the code snippet of my method:

public ActionResult GetCurrentState()
{

// create the entity object
ALCS4Entities entities = new ALCS4Entities();

// Get the InterConnectPortGidAB column data
var checkInterConnectPorts = (from icp in entities.LogicalConnectionEndToEnd_v
select new
{
InterconnectPortGidA = icp.InterconnectPortGidA,
InterconnectPortGidB = icp.InterconnectPortGidB
}).ToList();

return Json(new { interConnectPortGidABList = checkInterConnectPorts}, JsonRequestBehavior.AllowGet);

}

Answer

You can use where directly after the from entity and before the select:

var checkInterConnectPorts = (from icp in entities.LogicalConnectionEndToEnd_v
                              where InterconnectPortGidA != null 
                                 && InterconnectPortGidB != null
                              select new
                              {
                                  InterconnectPortGidA = icp.InterconnectPortGidA,
                                  InterconnectPortGidB = icp.InterconnectPortGidB
                              }).ToList();

This will get translated via the LINQ to SQL to a SQL statement, so will run on the SQL server directly, ie be the most efficient.

The query is deferred, it runs only when the query is executed, in this case at the time of the .ToList().

Comments