Hi All

I don't know if this will help anyone but at the agency I work at right now; we do a lot of physical data access with Umbraco using EF.

One of the main reasons for this is to push content to mobile devices using OData/Azure Mobile Services as well as generally syncing content from Umbraco to other systems like CRMs.

And this is the query I use to get all the latest fields for a given document type ID (hope this helps someone):

        var contentTypes = new[] { 4729 };

    cmsContents
    .Where(c => contentTypes.Contains( c.contentType ) )
    .Where(c => !c.umbracoNode.trashed)
    .OrderByDescending(c => c.umbracoNode.sortOrder)
    .Select(c => c
                .umbracoNode
                .cmsPropertyDatas
                .Where(v => v.versionId == c
                    .umbracoNode
                    .cmsDocuments
                    .FirstOrDefault(d => d.newest)
                    .versionId)
            )
    .SelectMany(g => g)
    .Select(d => new
    {
        Name =
                        d.umbracoNode.cmsDocuments.FirstOrDefault(
                            c => c.newest)
                            .text,

        ParentId = d.umbracoNode.parentID,
        //Parent = umbracoNodes.FirstOrDefault(n => n.id == d.umbracoNode.parentID ) ,
        Version = cmsContentVersions
            .Count(v => v.ContentId == d.contentNodeId),
        Publish = cmsDocuments.FirstOrDefault(c => c.newest).releaseDate,
        Unpublish = cmsDocuments.FirstOrDefault(c => c.newest).expireDate,
         Updated = cmsContentVersions
                        .Where(v => v.ContentId == d.contentNodeId)
                        .OrderBy(pd => pd.VersionDate)
                        .FirstOrDefault().VersionDate,
        Created = cmsContentVersions
                        .Where(v => v.ContentId == d.contentNodeId)
                        .OrderByDescending(pd => pd.VersionDate)
                        .FirstOrDefault().VersionDate,
        Author = umbracoUsers.FirstOrDefault(u => u.id == d.umbracoNode.nodeUser.Value).userEmail,
        DataNText = d.dataNtext,
        DataInt = d.dataInt,
        DataDate = d.dataDate,
        Id = d.umbracoNode.id,
        Trashed = d.umbracoNode.trashed,
        DataNVarChar = d.dataNvarchar,
        PropertyType = d.propertytypeid,
        PropertyName = d.cmsPropertyType.Name,
        PropertyAlias = d.cmsPropertyType.Alias,
        RetailerName = d.umbracoNode.text,
        DataType = cmsDataTypes
            .FirstOrDefault(t => t.nodeId == d.cmsPropertyType.dataTypeId)
            .propertyEditorAlias
    })
    .ToList()
    .GroupBy(r => r.Id)
    .Dump();

If you want to get a breakdown of DTs, their counts and IDs try this:

cmsContents
    .GroupBy(c => c.contentType)
    .Select(g => new {
        Id = g.Key, 
        Name = cmsContentTypes.FirstOrDefault(ct => ct.nodeId==g.FirstOrDefault().contentType).alias,
        Count = g.Count()
    } )
    .OrderByDescending(g => g.Count)

Regards,

Tim Scarfe