OData UnTyped Provider with SQL Backing Store

Question

I have a table in a database which is a store of Key/Value pairs, with some type information on the type of data stored in the value field.

I've read the blog series on creating an untyped data provider, and have grabbed and messed with the samples from odata, but I can not figure out how to turn the sample which uses an in-memory Dictionary as it's backing store, to use either EF or Linq to Sql entities as the actual store of data.

Answer 1

Ok, here is what I have so far. The only issue is that all the query has to be performed in memory:

public IList GetResourceSetEntities(string resourceSetName) { List entities;

        if (!this.resourceSetsStorage.TryGetValue(resourceSetName, out entities)) {
            entities = new List<DSPResource>();
            var resourceType = _metaData.ResourceSets.Single(r => r.Name == resourceSetName).ResourceType;
            var query = from v in _db.ChannelValues.GroupBy(x => x.ItemSetId)
                        select new DSPResource(resourceType,v.Key.Value, v);
            entities.AddRange(query.ToList());
            this.resourceSetsStorage[resourceSetName] = entities; 
        }
        return entities;
    }

Answer 2

What you're asking is rather hard. The reason the samples use Dictionary is to avoid writing custom LINQ provider (IQueryable implementation). In your case that's pretty much what you need to do. In addition it seems that neither EF nor LINQ to SQL will work for you because both assume statis data models where entities are rows in a table and properties are columns of that table (roughly speaking). That doesn't seem to be the case for you. If I understand it correctly you have a row per property in your model. To make this work you would have to implement at least partially IQueryable and make it understand your data structure. For example filtering is different, typically a simple filter on a Name property can be expressed in DB as a WHERE Name='...', but in your case this probably translates into a join where you need to search to key/value table for the row with the Name and then compare its value. You can either use the approach you suggested above, which loads everything into memory. It's rather simple to code, but it loads everything into memory. Or you can try writing a custom LINQ provider. This is very hard. But if you want to try it anway, I would suggest you take a look at this blog series which basically describes how something like LINQ to SQL could be implemented: http://blogs.msdn.com/b/mattwar/archive/2007/07/30/linq-building-an-iqueryable-provider-part-i.aspx I'm writing a series which describes what typ eof expressions you provider needs to support in order for it to work with WCF Data Services. That can come handy as well: http://blogs.msdn.com/b/vitek/archive/2010/02/25/data-services-expressions-part-1-intro.aspx Note that it is possible to write only portions of the provider yourself. You could for example deal with filtering yourself and leave the complex projections/expansions up to LINQ to Objects. This approach can make your provider a bit easier to implement.