CODEWITHSUNDEEP
c#linq
whytheq
whytheq

Reputation: 35557

LINQ Query baby-steps

I have the following code. It runs fine.

In the place I have marked I'd like to write a query (I assume with LINQ) which extracts the CompanyName where the MainKey == 3028

I suspect this is trivial but I'm new to LINQ and I've looked up some basic LINQ info on MSDN and can't seem to get it to 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; }

}

}

Upvotes: 4

Views: 261

Answers (3)

Adil
Adil

Reputation: 148120

The linq query would be.

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

Upvotes: 4

tomfanning
tomfanning

Reputation: 9660

The LINQ query below will post-process the IEnumerable you're generating from the T-SQL query, returning a single matching 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 you would be better off using LINQ-to-SQL and actually getting LINQ to generate and execute a T-SQL query for you.

Upvotes: 1

Knaģis
Knaģis

Reputation: 21485

Add using System.Linq

The query should 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 you have to remember - here you are not using LINQ queries to the SQL server - instead you are retrieving all data in memory and then filtering them in .NET. What this means is that if the database contains millions of rows, they will all be pulled from the SQL server. You are applying TOP 100 but that will get you into trouble if the key 3028 is not within the first 100.

What you should be doing is creating a model using Entity Framework (or a similar tool) and then writing a query that target the classes generated by it. The good thing though is that the LINQ query will be exactly the same - it will just be translated to SQL behind the scenes.

Upvotes: 7

Related Questions