LINQ - Different results with LINQ to SQL vs LINQPad

I am executing the "same" query using LINQ to SQL and in LINQPad, however the result set comes out slightly differently.

The code is

var conn = new SqliteConnection (
            "DbLinqProvider=Sqlite;" + 
            "Data Source=/home/larsenma/database.golden"
        );

Models.Main db = new Models.Main (conn);

var runSum =
  from 开发者_运维百科rr in db.Runrecords
  group rr by rr.RunID into rg          
  select new 
  {
    LaneCount = rg.Count(),
  }

LINQPad properly calculates the "LaneCount" while with LINQ to SQL I get 1s for each record.

With LINQ to SQL I used sqlmetal to generate my mapping for DBLinq and with LINQPad is used the IQ driver. My database is SQLite.

I'm rather new to this LINQ stuff, any ideas where I am going wrong?

EDITS

I've simplified my query to the minimalist reproducible instance. When I debug the generated SQL I get

SELECT (SELECT COUNT(*))
FROM runrecords
GROUP BY RunID

It is the second "COUNT(*)" that is missing things up.

Thanks.

---

It sounds like you're using DBLinq, not LINQ to SQL. The sqlmetal tool generates entity classes and a typed DataContext - however if you're using DBLinq, then it's DBLinq that's actually translating LINQ into SQL.

When I last looked at DBLinq (a year or two ago) it was fairly primitive and fell over on even some basic queries. LINQPad's IQ driver uses Matt Warren's IQueryable toolkit which is probably the most sophisticated provider-agnostic engine available for translating LINQ into SQL. Another good engine is DevArt's LINQ Connect library - this has come a long way in recent times and also supports SQLite.

Right now I'm in the process of updating the IQ driver to support Oracle, and also plan on posting some enhancements to the IQueryable library itself. Why don't you try using either this or DevArt's library.