LINQ Query baby-steps

Question

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; }

}

}

A great question to highlight how many different ways there are of doing such things with LINQ :) — Nick, Nov 06 '12 at 16:46
@Nick ...didn't realize; now I'm either spoiled for choice, or lost; depends on point-of-view — whytheq, Nov 06 '12 at 17:07
Hehe. No you're not lost, you're on the right track. What is highlighted below in particular is using either fluent or expression syntax. The end result is the same, but they provide interesting alternatives. Have a read here: http://stackoverflow.com/questions/214500/which-linq-syntax-do-you-prefer-fluent-or-query-expression — Nick, Nov 06 '12 at 18:38
@Nick +1 thanks for the help so far. Just one quick question; I see now that the structure in this post is _not_ entity framework (I see now that that is a separate Project in VS). The structure in this post is not old style `ADO` code using a `DataSet` - how is the structure in the OP described? — whytheq, Nov 10 '12 at 12:17
Not sure I understand what you mean by structure here? As pointed out below, you're somewhat between two technologies in what you are trying to achieve. What you should look to do is query against an Entity Framework context instead, because that creates an efficient SQL query for you based on your LINQ. — Nick, Nov 10 '12 at 14:08
thanks Nick - had a feeling that the code in the OP was neither one thing or the other. I'll attempt to create an entity framework project in the solution as query that instead. — whytheq, Nov 10 '12 at 16:48
No problems mate. Let me know if you'd like more advice. I can also put a small sample solution together for you if you think that would help. — Nick, Nov 10 '12 at 21:36

score 7 · Accepted Answer · answered Nov 06 '12 at 16:37

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.

ok - `What you should be doing is creating a model using Entity Framework (or a similar tool)` ....sounds a little scary! — whytheq, Nov 06 '12 at 16:42
It is scary... I would advise viewing a few videos on the matter like ones mentioned here http://stackoverflow.com/questions/3661915/entity-framework-video-tutorials — Knaģis, Nov 06 '12 at 16:48

Adil · Answer 2 · 2012-11-06T16:48:37.957

4

The linq query would be.

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

edited Nov 06 '12 at 16:48

answered Nov 06 '12 at 16:37

Adil

146,340
25
209
204

instead of `WHData.dbo.vw_DimCasino` do you mean `models` ? – whytheq Nov 06 '12 at 16:47

score 1 · Answer 3 · answered Nov 06 '12 at 16:41

1

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.

answered Nov 06 '12 at 16:41

tomfanning

9,552
4
50
78

one should always select just the column needed within the query and not retrieve the whole object. it has no difference when querying enumerables but it can have significant performance difference when targeting SQL – Knaģis Nov 06 '12 at 16:53

LINQ Query baby-steps

3 Answers3