//unbounded result set - there could be 1000s!
var products = category.Products
    .OrderBy(p => p.ProductName) //you have to order
    .Skip((page - 1) * pageSize)
    .Take(pageSize);

var pagedProductsByCategory = context.Products
    //have to specify primary keys here- can't match on "category"
    .Where(p => p.Category.CategoryId == category.CategoryId)
    .OrderBy(p => p.ProductName) //you have to order
    .Skip((page - 1) * pageSize)
    .Take(pageSize);

var pagedProducts = context.Entry(category)
    //from the DbEntityEntry, get the navigation property
    .Collection(x => x.Products)
    //turn it into a query
    .Query()
    //page
    .OrderBy(p => p.ProductName) //you have to order
    .Skip((page - 1) * pageSize)
    .Take(pageSize);

HasOptional(x => x.Category)
    .WithMany() //.WithMany(c => c.Products)
    .Map(m => m.MapKey("CategoryID"));

using (var context = new NorthwindContext()))
{
    var category = context.Categories.Find(1); //Beverages
    Console.WriteLine(category.Products.Count);
}

This loads ALL the products for the category, and then counts them.

Fine for a small result set, not so good if you have 1000s of products per category.

Simple solution: use a filter on products.

Console.WriteLine(context.Products
    .Count(p => p.Category.CategoryId == category.CategoryId));

This generates SQL in the form "SELECT COUNT(*) FROM Products WHERE CategoryID = @p"

Alternative solution: use context.Entry with .Query()

Console.WriteLine(context.Entry(category)
    .Collection(x => x.Products)
    .Query()
    .Count());

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="NorthwindContext" 
         providerName="System.Data.SqlClient" 
         connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True" />
  </connectionStrings>
  <system.data>
    <DbProviderFactories>
      <add name="EF Tracing Data Provider" 
           invariant="EFTracingProvider" 
           description="Tracing Provider Wrapper" 
           type="EFTracingProvider.EFTracingProviderFactory, EFTracingProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" />
      <add name="EF Generic Provider Wrapper" 
           invariant="EFProviderWrapper" 
           description="Generic Provider Wrapper" 
           type="EFProviderWrapperToolkit.EFProviderWrapperFactory, EFProviderWrapperToolkit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" />
    </DbProviderFactories>
  </system.data>
</configuration>

        public NorthwindContext()
        {
            //default ctor, uses app.config connection string named "NorthwindContext"
        }
 
        public NorthwindContext(DbConnection connection)
            :base(connection,true)
        {
           //ctor uses for tracing 
        }

            using (var context = new NorthwindContext(
                CreateConnectionWrapper(@"name=NorthwindContext")))
            {
 
                //profile this!
                var product = context.ProductCollection.Find(1);
            }

private static DbConnection CreateConnectionWrapper(string nameOrConnectionString)
{
    var providerInvariantName = "System.Data.SqlClient";
    var connectionString = nameOrConnectionString;
    //name=connectionName format
    var index = nameOrConnectionString.IndexOf('=');
    if (index > 0 && nameOrConnectionString.Substring(0, index).Trim()
        .Equals("name", StringComparison.OrdinalIgnoreCase))
    {
        nameOrConnectionString = nameOrConnectionString
            .Substring(index + 1).Trim();
    }
    //look up connection string name
    var connectionStringSetting =
        ConfigurationManager.ConnectionStrings[nameOrConnectionString];
    if (connectionStringSetting != null)
    {
        providerInvariantName = connectionStringSetting.ProviderName;
        connectionString = connectionStringSetting.ConnectionString;
    }
    //create the special connection string with the provider name in it
    var wrappedConnectionString = "wrappedProvider=" + 
        providerInvariantName + ";" + 
        connectionString;
    //create the tracing wrapper
    var connection = new EFTracingConnection
                            {
                                ConnectionString = wrappedConnectionString
                            };
    //hook up logging here
    connection.CommandFinished +=
        (sender, args) => Console.WriteLine(args.ToTraceString());
    return connection;
}

EFTracingProviderConfiguration.LogToConsole = true;

Or you can hook up to log4net or EntLib logging to those tracing events.

I wanted to use a many-to-many relationship using Entity Framework Code First (v4.1/4.2).

Using pure code first such as this:

using (var context = new MyContext())
{
    var employee = new Employee { FirstName = "Homer", LastName = "Simpson" };
    var territory = new Territory { TerritoryDescription = "Springfield" };
    employee.Territories.Add(territory);
    context.Employees.Add(employee);

    context.SaveChanges();
}

results in a nice association table

How do you map existing database tables? Like Northwind's customer to customer demographic table relationship:

This is the code I want to write:

using (var context = new MyContext("name=Northwind"))
{

    var demo = new CustomerDemographic();
    demo.CustomerTypeID = "BERLIN";
    demo.CustomerDesc = "Berliner";
    context.CustomerDemographics.Add(demo);
    //link it to a customer by either end
    var alfki = context.Customers.Find("ALFKI");
    alfki.CustomerDemographics.Add(demo);

    context.SaveChanges();
}

We have to override DbContext's OnModelCreating and add some mapping. For CodeFirst, you map both sides of the relationship, so you can either put the mapping on Customer or CustomerDemographic - or even both if the mappings agree. A normal foreign key relationship is mapped with ".HasMany|HasOptional|HasRequired" followed by a ".WithMany|WithOptional|WithRequired".

So, from the CustomerDemographic entity, a many to many is just .HasMany(x=>x.Customers).WithMany(z=>z.CustomerDemographics).

In addition, we don't have standard names for our association table so we add a .Map element to specify the table and the left and right key columns.

Note the primary key of CustomerDemographics isn't the 'tableName'+"Id" convention that Code First will expect. So I have to define the key for that. As we have that end of the configuration, we'll define the mapping there.

Here's the code.

modelBuilder.Entity<CustomerDemographic>()
    //the key isn't standard so specify it
    .HasKey(x => x.CustomerTypeID)
    //define both sides of the relationship - HasMany.WithMany
    .HasMany(x => x.Customers)
    .WithMany(z => z.CustomerDemographics)
    //specify mapping information
    .Map(map =>
    {
        //the association table name
        map.ToTable("CustomerCustomerDemo");
        //the left side (fk to CustomerDemographic, the entity we're defining)
        map.MapLeftKey("CustomerTypeID");
        //the right side (fk to Customers, the other side)
        map.MapRightKey("CustomerID");
    }
);

If we mapped from the Customer entity, the HasMany and WithMany properties are different, and the mapped left and right keys swap round.

Here's the full DbContext for my mini-Northwind mapping:

class MyContext : DbContext
{
    public MyContext(string connectionName)
        : base(connectionName)
    {
    }

    public DbSet<Employee> Employees { get; set; }
    public DbSet<Territory> Territories { get; set; }

    public DbSet<Customer> Customers { get; set; }
    public DbSet<CustomerDemographic> CustomerDemographics { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //Database.SetInitializer(new DropCreateDatabaseIfModelChanges<MyContext>());
        Database.SetInitializer<MyContext>(null);
        modelBuilder.Conventions.Remove<System.Data.Entity.Infrastructure.IncludeMetadataConvention>();

        modelBuilder.Entity<CustomerDemographic>()
            //the key isn't standard so specify it
            .HasKey(x => x.CustomerTypeID)
            //define both sides of the relationship - HasMany.WithMany
            .HasMany(x => x.Customers)
            .WithMany(z => z.CustomerDemographics)
            //specify mapping information
            .Map(map =>
            {
                //the association table name
                map.ToTable("CustomerCustomerDemo");
                //the left side (fk to CustomerDemographic, the entity we're defining)
                map.MapLeftKey("CustomerTypeID");
                //the right side (fk to Customers, the other side)
                map.MapRightKey("CustomerID");
            }
        );
    }
}

 
//find the working folder for each TFS
var projectCollections = RegisteredTfsConnections.GetProjectCollections();
 
foreach (var registeredProjectCollection in projectCollections)
{
    Console.WriteLine("Project collection: {0} {1}", 
registeredProjectCollection.Name, 
registeredProjectCollection.Uri.AbsoluteUri);
    var projectCollection =
        TfsTeamProjectCollectionFactory.GetTeamProjectCollection(registeredProjectCollection);
 
    var versionControl = projectCollection.GetService<VersionControlServer>();
 
    // get workspace
    var workspace =
        versionControl.QueryWorkspaces(null,
                System.Threading.Thread.CurrentPrincipal.Identity.Name,
                Environment.MachineName)
                .FirstOrDefault(x => 
                    x.Folders.Length > 0 &&
                    //if there is a Work Item Manager, we don't care
                    x.Name != "WIM (" + Environment.MachineName + ")");
    if(workspace == null) continue; //no workspace for this server
 
    //there's normally only one
    WorkingFolder folder = workspace.Folders.First();
    Console.WriteLine("Working folder {0}", folder.LocalItem);
}

//old VS 2008 way
//using (TeamFoundationServer tfsServer = new TeamFoundationServer(tfsServerName))
//{
//    // Get a reference to version control
//    VersionControlServer versionControl =
//        (VersionControlServer) tfsServer.GetService(typeof (VersionControlServer));
//    //....
//}

I've just done all 4 of the exams for .Net 4.0 MCPD Web Developer. Overall, I quite enjoyed it. I even learned a bit.

Programmer certifications, and particularly the Microsoft ones, get a lot of criticism. In theory they prove someone is proficient in the technology. In practice, by itself a certificate is unreliable.

Microsoft's Partner program insists that companies have staff with certifications. The body shops put their new recruits through a brief course so they can pass and then be sold on. Often, they memorised the questions and answers from internet dumps. It's disappointing to google the exam codes: apart from the Microsoft syllabus, every link is a site selling the answers.

If you're a consultant, or you're going to change jobs, experienced programmers probably have to do the exams too. If you work for a Microsoft Partner you have to do it, and HR departments filter their CVs using it. Really, the best measure of proficiency is years of experience and types of projects. That's real-world practical knowledge. Okay, I would say that, having been doing this for 25 years. But, as the company I work for needs the certificate points, and they're paying, I'm happy to do the exams.

For .net 1 we had to do exams for ASP, windows forms and web services, but most .net programmers then only worked in one or two of those- certainly never windows forms and ASP. I was actually quite impressed by the .Net 2 foundation exam, which covered a lot of basics: collections, threads, tracing, serialization, globalization, encryption. Specific projects may not involve some of it, but a good .net programmer should know almost all of it (even if you have to google the details). Unfortunately they dropped it for .Net 4, going back to technology areas (asp, web services) just like the original .net 1 exams.  Now for .net 4 "web" we have asp, wcf and data access (ado/entity framework). Broadly I agree you'd expect all web developers to know those topics, so that's not too bad.

One big problem with certification questions is that they pick on obscure APIs and ASP controls. Now, questions on the validation controls is probably a good basic requirement, but frankly the gridview events are a nightmare anyway, so memorizing them for an exam is painful. The .net 4 asp exam wasn't too bad although there was some API questions that in real life you'd just google. One of the jQuery questions annoyed me too, using an unusual API detail (I think an older syntax) when they could have used a better, clearer replacement.

The older exams had sections devoted to things nobody ever uses. On the asp .net 2 exam it was mobile controls, and web parts (cloned from Sharepoint, and never used outside Sharepoint). Fortunately there seems less of that in the .net 4 exams, although there's still some asp ajax library stuff in there, plus some dynamic data. The big problem was that jQuery and MVC have moved on since the exam was written. All the questions were about MVC 2, not 3. It's not that the questions were obsolete (there was nothing much about webform views to confuse those who use Razor), it's just that the pace of change is making exams less relevant.

The other exams had little used topics too. The WCF exam covered MSMQ, which I've never seen used in real life. The data access exam was more problematic. Basic ADO is useful, but there was still material about little used dataset features like constraints and dataRelations. There was not much on Linq2Sql, perhaps just as well, but most of the exam was Entity Framework. I know EF is used out there, but I've yet to encounter EF and it seems to be in second place to NHibernate at least for ORM data access. So I did learn something for this exam, and yes, I passed the exam as a novice with no real world experience of Entity Framework. On the other hand, as an experienced programmer I'd feel comfortable doing EF (even if I'd rather be doing NHibernate.)

Two of the exams - the WCF and the ASP Pro exam - had "testlets" with a case study and a small number of questions about the scenario. The WCF was over-elaborate, with source listings and xml, but for all that I thought it was a little more realistic and actually quite enjoyable.

To study, I used the Microsoft Training Kit books, MSDN and for the areas I was less familiar with - mostly EF and some aspects of WCF - a little practice. The WCF exam didn't have a book, so I had to use the .net 3.5 one and look up a couple of subjects (routing and discovery). I skimmed the bits I knew well (the asp/ web side generally). Generally I found you needed a bit more knowledge than was in the books, but only a few things were really obscure. Overall, studying for the exam was interesting and useful. Learning about WCF routing and discovery and EF, things I haven't actually used, did bring me more up-to-date with the .net 4 stack.

A pity Microsoft's "congratulations" email contains a link to a blog that closed 2 years ago.

//in MVC use HttpRuntime.Cache or HttpContext.Cache
//in webforms Application is the original cache without expiration rules
var category = HttpRuntime.Cache["Category"] as CategoryModel;
if (category == null)
{
     category = _dataAccess.Find(1);
     HttpRuntime.Cache["Category"] = category;
     // ...or...
     //monitor some files and/or other cache items
     var cd = new CacheDependency(new[] { @"C:\triggerFolder\" }, new[] { "OtherCacheItem" });
     HttpRuntime.Cache.Insert("Category1", category, 
               cd, //dependencies or null
               DateTime.Now.AddMinutes(5), //absolute expiration (or Cache.NoAbsoluteExporation)
             Cache.NoSlidingExpiration //sliding expiration (timespan)
     );
}

//get the static "default" cache. You can have multiple named caches.
ObjectCache cache = MemoryCache.Default;
//you can't store null in the cache
var category = cache["Category"] as CategoryModel;
if (category == null)
{
    category = _dataAccess.Find(1);
    cache["Category"] = category;
    // ...or...
    var policy = new CacheItemPolicy();
    policy.AbsoluteExpiration = DateTime.Now.AddMinutes(5);
    //monitor some files and/or other cache items
    policy.ChangeMonitors.Add(
        new HostFileChangeMonitor(new List<string> { @"C:\triggerFolder\"})
        );
    //synchronize with another cache item
    policy.ChangeMonitors.Add(
        cache.CreateCacheEntryChangeMonitor(new [] { "OtherCacheItem"})
        );
    cache.Add("Category1", category, policy);
}

<#@ template language="C#" #>
<#@ import namespace="System.Linq" #>
<#@ parameter type="Generator.Model.Table" name="table" #>
using System;
 
namespace <#= table.Namespace #>
{
    [Serializable]
    public class <#= table.Name #>
    {
<#  foreach(var column in table.Columns.Where(c=> !c.Hidden)) { #>
        public virtual <#= column.Type #> <#= column.Name #> { get; set; }
<#    } #>
    }
}

//create the class generated by TextTemplatingFilePreprocessor 
var generator = new ClassWriter();
//create a session dictionary, fill it and initialize
generator.Session = new Dictionary<string, object>();
generator.Session.Add("table", table);
generator.Initialize();
//transform!
var text = generator.TransformText();

• you must create the Session dictionary (it's not initialized internally)
• you must call Initialize() after it's populated.

//don't do this
var generator = new ClassWriter();
foreach (var item in list)
{
    generator.Session = new Dictionary<string, object>();
    generator.Session.Add("table", item);
    generator.Initialize();
    var txt = generator.TransformText();
    WriteText(item, txt);
}

Shortly after the last release of Database Schema Reader, here's another release.

Last time I added a CopyToSQLite tool, which reads (almost) any database and creates a SQLite clone. I also added experimental support for SQLServer CE 4.0, but it had some big limitations.

So the obvious next step was to fix some of those limitations, and that's what this release focuses on. Unfortunately CopyToSQLite.exe is now a little misnamed.

I added a little bit of conversion code so VARCHAR(MAX) found in the origin database gets converted to NTEXT (and VARBINARY to IMAGE). The reading of SQLServer CE 4 databases got improved too (capturing the  foreign and unique keys and identity columns).

SQLServer allows an integer column to be marked as "Identity" so it will be auto-generated (the equivalent in Oracle is to create a sequence and an insert trigger). But you can't include the identity column when you insert a row. So, when cloning data, SQLServer also allows identity-inserts with SET IDENTITY_INSERT [MyTable] ON/OFF. When you've done that, you should reset the identity seed with DBCC CHECKIDENT.

But in SQLServer CE 4, there is no DBCC CHECKIDENT. You have to do an ALTER TABLE [MyTable] ALTER COLUMN [IdentityColumn] IDENTITY (999,1) (where 999 is the new max(IdentityColumn)). It's another of those little gotchas between SQLServer and SQLServer CE.

SQLServer CE 4 has some great new paging syntax:

SELECT Id, Name FROM MyTable
ORDER BY Name
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY

It's similar to the LIMIT/OFFSET syntax in MySQL and SQLite, but (reasonably enough) must follow an ORDER BY. Rather than offset/skipping a number of rows, I'd like to specify just page number and page size - or another formula. The SQLServer 2011/Denali documentation shows the offset/fetch syntax for a start row/end row expression:

SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC
OFFSET @StartingRowNumber - 1 ROWS
FETCH NEXT @EndingRowNumber - @StartingRowNumber + 1 ROWS ONLY

But that doesn't work in SQLServer CE 4. You can use parameters, or constants, or expressions with constants, but apparently not expressions with constants and parameters. Oh well, it's still much better than horrible OVER subqueries.

Overall, the "CopyToSQLite" to SQLServer CE 4 seems to work well. Using easily deployable file databases like SQLServer CE and SQLite is simple and powerful.