LINQ Query baby-steps

Question

I have the following code. It works great.

In the place where I checked, I would like to write a query (suppose LINQ ) that extracts CompanyName where MainKey == 3028

I suspect this is trivial, but I'm new to LINQ , and I was looking for some basic LINQ information on MSDN and cannot make it work.

 namespace EntityFrameworkExperiment { class Program { static void Main(string[] args) { var models = SelectTop100Models("SELECT top 100 * FROM WH.dbo.vw_DimXXX"); Console.Write("hello world"); //<<<<<<<linq query to pull out companyname when MainKey == 3028 Console.Read(); } static IEnumerable<MyModel> SelectTop100Models(string myCommandText) { var connectionString = ConfigurationManager.ConnectionStrings["XXX"].ConnectionString; using(var conn = new SqlConnection(connectionString)) using(var cmd = conn.CreateCommand()) { conn.Open(); cmd.CommandText = myCommandText; using(var reader = cmd.ExecuteReader()) { while(reader.Read()) { yield return new MyModel { MainKey = reader.GetInt32(reader.GetOrdinal("MainKey")), ServerId = reader.GetInt32(reader.GetOrdinal("ServerId")), CompanyId = reader.GetInt32(reader.GetOrdinal("CompanyId")), CompanyName = reader.GetString(reader.GetOrdinal("CompanyName")), }; } } } } } public class MyModel { public int MainKey { get; set; } public int ServerId { get; set; } public int CompanyId { get; set; } public string CompanyName { get; set; } } } 

Answer 1

Add using System.Linq

The request must be

 var companyName = models .Where(o => o.MainKey == 3028) // apply the filter .Select(o => o.CompanyName) // tell it you only need the one property .FirstOrDefault(); // take the first result it finds or use 'null' if the MainKey does not exist 

But there is one thing that you should remember - here you do not use LINQ queries on the SQL server, instead you extract all the data in memory and then filter it in .NET. This means that if the database contains millions of rows, they will all be deleted from the SQL server. You are using TOP 100, but this will cause you problems if key 3028 is not in the first 100.

What you should do is create a model using the Entity Framework (or a similar tool) and then write a query designed for the classes generated by it. It’s good, however, that the LINQ query will be exactly the same - it will just be translated into SQL behind the scenes.

Answer 2

The linq request will be.

 var result = from rec in ModelOfWHData.vw_DimCasinos where (rec.MainKey == 3028) select rec.CompanyName 

Answer 3

The following is a LINQ query that will handle the IEnumerable that you generate from a T-SQL query, returning one suitable object, or null if not found:

 MyModel result = (from m in MyModel where m.MainKey == 3028 select m).SingleOrDefault(); string companyName = result.CompanyName; 

However, I suspect that you are better off using LINQ-to-SQL and getting LINQ to create and execute a T-SQL query for you.