OrientDB-NET.binary for models

Question

My team is interested in migrating an existing MySQL database (which happens to be extremely ugly and poorly indexed) to OrientDB. As our application is written in C#, I'm investigating our ability to access the database from within a C# application.

I've found OrientDB-NET.binary as a library to supposedly do what I want, however, I'm having trouble getting it to do everything I desire. The database I'm testing with is very simple, from the OrientDB documentation:

create class Person extends V
create class Car extends V
create class Country extends V

create class Owns extends E
create class Lives extends E

create property Owns.out LINK Person
create property Owns.in LINK Car
create property Lives.out LINK Person
create property Lives.in LINK Country

alter property Owns.out MANDATORY=true
alter property Owns.in MANDATORY=true
alter property Lives.out MANDATORY=true
alter property Lives.in MANDATORY=true

create index UniqueOwns on Owns(in) unique
create index UniqueLives on Lives(out) unique

When I query the DB for ODocuments, I get plenty of expected information:

OClient.CreateDatabasePool("127.0.0.1", 2424, "cars",
    ODatabaseType.Graph, "admin", "admin", 10, "Cars");
using (ODatabase db = new ODatabase("Cars"))
{
    var results = db.Select().Form("Country").ToList();
    foreach (var item in results)
    {
        foreach(var key in item.Keys)
        {
            Console.WriteLine(key);
            if (item[key] is IEnumerable<ORID>)
            {
                foreach (var element in item[key] as IEnumerable<ORID>)
                {
                    Console.WriteLine("   " + element);
                }
            }
            else
            {
                Console.WriteLine("   " + item[key]);
            }
        }
        Console.WriteLine();
    }
}
Console.ReadKey();

Output:

@ORID
   #15:0
@OVersion
   5
@OType
   Document
@OClassId
   0
@OClassName
   Country
Name
   UK
in_Lives
   #16:0

@ORID
   #15:1
@OVersion
   3
@OType
   Document
@OClassId
   0
@OClassName
   Country
Name
   US
in_Lives
   #16:1
   #16:2

If I create a class to represent my vertices, I can query for them:

public class Person
{
    public string Name { get; set; }
}

public class Country
{
    public string Name { get; set; }
}

public class Car
{
    public string Name { get; set; }
}

//...

var results = db.Select().From("Country").ToList<Country>();
foreach (var item in results)
{
    Console.WriteLine(item.Name);
}

Output:

UK
US

However, I want to be able to access the connections between my vertices:

public class Person
{
    public string Name { get; set; }
    public Country Lives { get; set; }
    public IEnumerable<Car> Owns { get; set; }
}

public class Country
{
    public string Name { get; set; }
    public IEnumerable<Person> Lives { get; set; }
}

public class Car
{
    public string Name { get; set; }
    public Person Owns { get; set; }
}

These parameters won't be populated by the query. However, I can add ORID lists for the edges:

public class Person
{
    public string Name { get; set; }
    public List<ORID> out_Lives { get; set; }
    public List<ORID> out_Owns { get; set; }
}

public class Country
{
    public string Name { get; set; }
    public List<ORID> in_Lives { get; set; }
}

public class Car
{
    public string Name { get; set; }
    public List<ORID> in_Owns { get; set; }
}

This doesn't work if I simply try to query the vertex:

db.Select().From("Person").ToList<Person>();

Different parts of the library are apparently requiring different types on the edge lists. (If I recall correctly, one part requires them to be Lists, one part requires them to be HashSets, and one part requires them to have a parameterless constructor.) But it does work if I query the edges specifically:

db.Select("Name").As("Name")
    .Also("out('Owns')").As("out_Owns")
    .Also("out('Lives')").As("out_Lives")
  .From("Person").ToList<Person>();

Unfortunately, the ORID class doesn't have any of the information I need; it's just a reference which I can use in other queries.

My last-ditch solution was to try creating shim properties which would query the database based on the RIDs, just to see if I could get things working:

public class Person
{
    public string Name { get; set; }
    public List<ORID> out_Owns { get; set; }
    public List<ORID> out_Lives { get; set; }

    public IEnumerable<Car> Owns
    {
        get
        {
            if (owns != null &&
                  out_Owns != null && owns.Count() == out_Owns.Count) return owns;
            owns = new List<Car>();
            if (out_Owns == null || out_Owns.Count == 0) return owns;

            using (ODatabase db = new ODatabase("Cars"))
            {
                owns = db.Select("Name").As("Name")
                           .Also("in('Owns')").As("in_Owns")
                         .From(out_Owns.First()).ToList<Car>();
            }
            return owns;
        }
    }

    public Country Lives
    {
        get
        {
            if (lives != null) return lives;
            if (out_Lives == null || out_Lives.Count == 0) return null;

            using (ODatabase db = new ODatabase("Cars"))
            {
                lives = db.Select("Name").As("Name")
                            .Also("in('Lives')").As("in_Lives")
                          .From(out_Lives.First()).ToList<Country>()
                          .FirstOrDefault();
            }
        }
    }

    private IEnumerable<Car> owns;
    private Country lives;
}

public class Country
{
    public string Name { get; set; }
    public List<ORID> in_Lives { get; set; }

    public IEnumerable<Person> Lives
    {
        get
        {
            if (lives != null &&
                  in_Lives != null && lives.Count() == in_Lives.Count) return lives;
            lives = new List<Person>();
            if (in_Lives == null || in_Lives.Count == 0) return lives;

            using (ODatabase db = new ODatabase("Cars"))
            {
                StringBuilder sb = new StringBuilder();
                foreach (ORID id in in_Lives)
                {
                    sb.AppendFormat("{0},", id);
                }
                if (sb.Length > 0)
                {
                    sb.Remove(sb.Length - 1, 1);
                }
                lives = db.Select("Name").As("Name")
                            .Also("out('Owns')").As("out_Owns")
                            .Also("out('Lives')").As("out_Lives")
                          .From(string.Format("[{0}]", sb)).ToList<Person>();
            }
            return lives;
        }
    }

    private IEnumerable<Person> lives;
}

public class Car
{
    public string Name { get; set; }
    public List<ORID> in_Owns { get; set; }

    public Person Owns
    {
        get
        {
            if (owns != null) return owns;
            if (in_Owns == null || in_Owns.Count == 0) return null;

            using (ODatabase db = new ODatabase("Cars"))
            {
                owns = db.Select("Name").As("Name")
                           .Also("out('Owns')").As("out_Owns")
                           .Also("out('Lives')").As("out_Lives")
                         .From(in_Owns.First()).ToList<Person>()
                         .FirstOrDefault();
            }
        }
    }

    private Person owns;
}

This functions, but it looks like utterly horrible code. I'm querying the database in my get accessors (although caching the result mitigates that problem somewhat), and doing so in a way that requires knowing the properties of the linked vertices, in a means that can't be caught at compile time. These problems will only increase as the scale of the database goes up, both in number of records (one of the MySQL tables we'll be migrating currently has over 7m rows) and number of properties (although it will probably be broken into several classes, one of the MySQL tables has over 100 columns).

I would like to be able to simply call db.Select().From("MyVertex").ToList<MyVertex>(); and get a list of MyVertex objects with properties for the edges coming out of the vertex in the graph. Is this possible with this library? Is this possible with any C# library?

Answer 1

So, as @Roman says, the graph database isn't exactly designed to achieve what I'm looking for. However, I've developed some extension methods that produce the result anyway, using traverse.

As a prerequisite, all models to be used with this solution need to extend ABaseModel, be in the same namespace as ABaseModel, and have a parameterless constructor. The base class:

using Orient.Client;

namespace MyApplication
{
    public abstract class ABaseModel
    {
        public ORID ORID { get; set; }
        public int OVersion { get; set; }
        public ORecordType OType { get; set; }
        public short OClassId { get; set; }
        public string OClassName { get; set; }
    }
}

This simply provides a common base for the extension methods, as well as including all of the reserved properties for the model when mapping with TypeMapper.

That done, the methods Traverse<T>(this ODatabase, string) and Traverse<T>(this ODatabase, ORID) extend ODatabase to provide desired functionality:

using Orient.Client;
using Orient.Client.Mapping;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;

namespace MyApplication
{
    /// <summary>
    /// Provides extension methods for <see cref="Orient.Client.ODatabase"/>
    /// </summary>
    public static class DatabaseExtensions
    {
        private const int SINGLE_RID_TARGET_PATTERN_INDEX = 3;
        private static readonly string[] legalTargets = {
            @"^(?:class:)?[a-zA-Z][a-zA-Z0-9]*$",       // Class
            @"^cluster:\d+$",                           // Root cluster
            @"^\[(?:#\d+:\d+\s*,?\s*)*(?:#\d+:\d+)\]$", // Array of RIDs
            @"^#\d+:\d+$"                               // Single root record RID
        };

        /// <summary>
        /// Fills out a collection of models of type <typeparamref name="T"/> using <c>traverse</c>. <paramref name="db"/> must be open.
        /// </summary>
        /// <remarks>
        /// <para>Note that <c>traverse</c> can be slow, and <c>select</c> may be more appropriate. See
        /// http://www.orientechnologies.com/docs/last/orientdb.wiki/SQL-Traverse.html#should-i-use-traverse-or-select
        /// </para>
        /// <para>Lightweight edges are not followed when populating model properties. Make sure to use "heavyweight" edges with either
        /// <c>alter property MyEdgeClass.out MANDATORY=true</c> and <c>alter property MyEdgeClass.in MANDATORY=true</c>, or else
        /// use <c>alter database custom useLightweightEdges=false</c>.</para>
        /// </remarks>
        /// <typeparam name="T">The model type. Must extend <see cref="ABaseModel"/>, have a parameterless constructor, and most importantly it must be in the same
        /// namespace as <see cref="ABaseModel"/>.</typeparam>
        /// <param name="db">The database to query</param>
        /// <param name="from">A class, cluster, RID list, or RID to traverse. RIDs are in the form <c>#clusterId:clusterPosition</c>. Lists are in the form
        /// <c>[RID,RID,...]</c> with one or more elements (whitespace is ignored). Clusters are in the form <c>cluster:clusterName</c> or <c>cluster:clusterId</c>.</param>
        /// <exception cref="System.ArgumentException">If <paramref name="from"/> is an invalid format</exception>
        /// <returns>An enumerable collection of models of type <typeparamref name="T"/>. Public instance properties of the models will have their values populated
        /// based on all non-lightweight edges in the traversal.</returns>
        public static IEnumerable<T> Traverse<T>(this ODatabase db, string from) where T : ABaseModel, new()
        {
            // Sanity check on target
            bool matches = false;
            foreach (string pattern in legalTargets)
            {
                if (Regex.IsMatch(from, pattern))
                {
                    matches = true;
                    break;
                }
            }

            if (!matches)
            {
                throw new ArgumentException("Traverse target must be a class, cluster, RID list, or single RID.", "from");
            }

            bool fromSingleRecord = Regex.IsMatch(from, legalTargets[SINGLE_RID_TARGET_PATTERN_INDEX]);

            // Traverse DB
            string sql = string.Format("traverse * from {0}", from);
            List<ODocument> result = db.Query(sql);
            DatabaseTraversal traversal = new DatabaseTraversal(db, result);

            // Process result
            IEnumerable<T> models = traversal.ToModel<T>();
            if (fromSingleRecord)
            {
                // Either Traverse(ORID) was called, or client code called Traverse with an RID string -- return a single element
                models = models.Where(m => m.ORID.ToString().Equals(from));
            }
            return models;
        }

        /// <summary>
        /// Fills out a model of type <typeparamref name="T"/> using <c>traverse</c>. <paramref name="db"/> must be open.
        /// </summary>
        /// <remarks>
        /// <para>Note that <c>traverse</c> can be slow, and <c>select</c> may be more appropriate. See
        /// http://www.orientechnologies.com/docs/last/orientdb.wiki/SQL-Traverse.html#should-i-use-traverse-or-select
        /// </para>
        /// <para>Lightweight edges are not followed when populating model properties. Make sure to use "heavyweight" edges with either
        /// <c>alter property MyEdgeClass.out MANDATORY=true</c> and <c>alter property MyEdgeClass.in MANDATORY=true</c>, or else
        /// use <c>alter database custom useLightweightEdges=false</c>.</para>
        /// </remarks>
        /// <typeparam name="T">The model type. Must extend <see cref="ABaseModel"/>, have a parameterless constructor, and most importantly it must be in the same
        /// namespace as <see cref="ABaseModel"/>.</typeparam>
        /// <param name="db">The database to query</param>
        /// <param name="from">The root RID to traverse.</param>
        /// <returns>A model representing the record indicated by <paramref name="from"/>.</returns>
        public static T Traverse<T>(this ODatabase db, ORID from) where T : ABaseModel, new()
        {
            // Traverse<T>(from.ToString()) is guaranteed to have 0 or 1 elements
            return db.Traverse<T>(from.ToString()).SingleOrDefault();
        }

        /// <summary>
        /// Helper class for traversing <see cref="Orient.Client.ODatabase"/>
        /// </summary>
        private class DatabaseTraversal
        {
            private IEnumerable<ODocument> documents;
            private IEnumerable<OEdge> edges;
            private IDictionary<ORID, ODocument> documentMap;

            private static readonly Func<Type, bool> isModelPropertyEnumerableHelper = pType => typeof(System.Collections.IEnumerable).IsAssignableFrom(pType);
            private static readonly Func<PropertyInfo, string> isModelPropertyHelper = pInfo =>
            {
                string alias = pInfo.Name;
                OProperty propertyAlias = pInfo.GetCustomAttributes(typeof(OProperty)).Where(attr => !string.IsNullOrEmpty(((OProperty)attr).Alias)).SingleOrDefault() as OProperty;
                if (propertyAlias != null)
                {
                    alias = propertyAlias.Alias;
                }

                return alias;
            };
            private static readonly Action<dynamic, dynamic, string> setPropertiesHelper = (parent, child, className) =>
            {
                PropertyInfo[] properties = parent.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty | BindingFlags.GetProperty);
                PropertyInfo propertySingle = properties.Where(prop => IsModelProperty(prop, className)).SingleOrDefault();
                PropertyInfo propertyCollection = properties.Where(prop => IsModelCollectionProperty(prop, className)).SingleOrDefault();
                if (propertySingle != null)
                {
                    propertySingle.SetValue(parent, child);
                }
                else if (propertyCollection != null)
                {
                    dynamic propertyValue = propertyCollection.GetValue(parent);
                    if (propertyValue == null)
                    {
                        Type listOfT =  typeof(List<>).MakeGenericType(propertyCollection.PropertyType.GenericTypeArguments[0]);
                        IEnumerable collection = (IEnumerable)Activator.CreateInstance(listOfT);
                        propertyValue = collection;
                        propertyCollection.SetValue(parent, collection);
                    }

                    propertyValue.Add(child);
                }
            };

            /// <summary>
            /// Create new <see cref="DatabaseTraversal"/> object. <paramref name="database"/> must be open.
            /// </summary>
            /// <param name="database">Database to traverse. Required for discovering edges.</param>
            /// <param name="documents">Documents produced by <c>traverse * from $target</c></param>
            public DatabaseTraversal(ODatabase database, IEnumerable<ODocument> documents)
            {
                this.documents = documents;
                documentMap = documents.ToDictionary<ODocument, ORID>(doc => doc.ORID);
                // Need to know which RIDs in documentMap are edges
                edges = database.Select().From("E").ToList<OEdge>().Where(edge => documentMap.ContainsKey(edge.ORID));
            }

            /// <summary>
            /// Populate model object(s)
            /// </summary>
            /// <typeparam name="T">Type of model to return</typeparam>
            /// <returns>A collection of model objects which appear in the traversal.</returns>
            public IEnumerable<T> ToModel<T>() where T : ABaseModel, new()
            {
                if (documents.Count() == 0) return null;

                IDictionary<ORID, ABaseModel> models = new Dictionary<ORID, ABaseModel>();
                foreach (OEdge e in edges)
                {
                    ODocument outDoc = documentMap[e.OutV];
                    ODocument inDoc = documentMap[e.InV];

                    dynamic outModel, inModel;
                    bool containsOutId = models.ContainsKey(outDoc.ORID);
                    bool containsInId = models.ContainsKey(inDoc.ORID);

                    // Set the value for the models that edge is pointing into/out of
                    if (containsOutId)
                    {
                        outModel = models[outDoc.ORID];
                    }
                    else
                    {
                        outModel = GetNewPropertyModel(typeof(T).Namespace, outDoc.OClassName);
                        MapProperties(outDoc, outModel);
                        models.Add(outModel.ORID, outModel);
                    }

                    if (containsInId)
                    {
                        inModel = models[inDoc.ORID];
                    }
                    else
                    {
                        inModel = GetNewPropertyModel(typeof(T).Namespace, inDoc.OClassName);
                        MapProperties(inDoc, inModel);
                        models.Add(inDoc.ORID, inModel);
                    }

                    // Set the property values for outModel to inModel if they exist
                    setPropertiesHelper(outModel, inModel, e.OClassName);
                    setPropertiesHelper(inModel, outModel, e.OClassName);
                }

                // Return models of type T
                IEnumerable<T> result = models.Select(kvp => kvp.Value).Where(model => model.OClassName.Equals(typeof(T).Name)).Cast<T>();
                return result;
            }

            /// <summary>
            /// Map non-edge properties of the vertex to the model
            /// </summary>
            /// <typeparam name="T">The model type</typeparam>
            /// <param name="document">The vertex</param>
            /// <param name="resultObj">The model object</param>
            private static void MapProperties<T>(ODocument document, T resultObj)
            {
                (TypeMapperBase.GetInstanceFor(typeof(T)) as TypeMapper<T>).ToObject(document, resultObj);
            }

            /// <summary>
            /// Create a new instance of a model type
            /// </summary>
            /// <param name="nSpace">The model's namespace</param>
            /// <param name="modelName">The model's class name</param>
            /// <returns>A newly-initialized instance of the class <c>nSpace.modelName</c></returns>
            private static dynamic GetNewPropertyModel(string nSpace, string modelName)
            {
                Type modelClass = Type.GetType(string.Format("{0}.{1}", nSpace, modelName));
                return modelClass.GetConstructor(Type.EmptyTypes).Invoke(null);
            }

            /// <summary>
            /// Checks whether the given property or its alias is a vertex's class name and is not enumerable
            /// </summary>
            /// <param name="currentProperty">The property to compare name/alias against. Aliases should be set with <see cref="Orient.Client.OProperty"/></param>
            /// <param name="name">The vertex class name to compare against</param>
            /// <returns><see langword="true"/> if <paramref name="currentProperty"/> is named <paramref name="namne"/> or has an <see cref="Orient.Client.OProperty"/>
            /// attribute with an alias of <paramref name="name"/>, and <paramref name="currentProperty"/> is not a collection type.</returns>
            private static bool IsModelProperty(PropertyInfo currentProperty, string name)
            {
                string alias = isModelPropertyHelper(currentProperty);
                return !isModelPropertyEnumerableHelper(currentProperty.PropertyType) && alias.Equals(name);
            }

            /// <summary>
            /// Checks whether the given property or its alias is a vertex's class name and is enumerable
            /// </summary>
            /// <param name="currentProperty">The property to compare name/alias against. Aliases should be set with <see cref="Orient.Client.OProperty"/></param>
            /// <param name="name">The vertex class name to compare against</param>
            /// <returns><see langword="true"/> if <paramref name="currentProperty"/> is named <paramref name="namne"/> or has an <see cref="Orient.Client.OProperty"/>
            /// attribute with an alias of <paramref name="name"/>, and <paramref name="currentProperty"/> is a collection type.</returns>
            private static bool IsModelCollectionProperty(PropertyInfo currentProperty, string name)
            {
                string alias = isModelPropertyHelper(currentProperty);
                return isModelPropertyEnumerableHelper(currentProperty.PropertyType) && alias.Equals(name);
            }
        }
    }
}

Example of usage:

Person luca = db.Traverse<Person>(new ORID("#12:0"));
// luca.Name == "Luca"
// luca.Owns != null
// luca.Owns.Count == 1
// luca.Owns[0].Name == "Ferrari Modena"
// luca.Owns[0].Owner == luca
// luca.Lives != null
// luca.Lives.Name == "UK"
// luca.Lives.Residents != null
// luca.Lives.Residents.Count == 2
// luca.Lives.Residents[0] == luca
// luca.Lives.Residents[1].Lives.Residents[0] == luca
// luca.Lives.Residents[1].Owns == null -> because there is no edge to any Car

Answer 2

Pushed patch to OrientDB-NET.binary

Could you please try with latest version ?

Look for this example

using System;
using System.Linq;
using System.Collections.Generic;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Orient.Client;

namespace Orient.Tests.Issues
{
    // http://stackoverflow.com/questions/26661636/orientdb-net-binary-for-models

    [TestClass]
    public class StackOverflow_q_26661636
    {
        TestDatabaseContext _context;
        ODatabase _database;

        [TestInitialize]
        public void Init()
        {
            _context = new TestDatabaseContext();
            _database = new ODatabase(TestConnection.GlobalTestDatabaseAlias);

            _database.Create.Class<Person>().Extends<OVertex>().CreateProperties().Run();
            _database.Create.Class<Country>().Extends<OVertex>().CreateProperties().Run();
            _database.Create.Class<Car>().Extends<OVertex>().CreateProperties().Run();
            _database.Create.Class("Owns").Extends<OEdge>().Run();
            _database.Create.Class("Lives").Extends<OEdge>().Run();

        }

        [TestCleanup]
        public void Cleanup()
        {
            _database.Dispose();
            _context.Dispose();
        }

        [TestMethod]
        [TestCategory("Stackoverflow")]
        public void q_26661636()
        {
            var lukaPerson = new Person { Name = "Luca" };
            var lpV = _database.Create.Vertex(lukaPerson).Run();

            var ferrariModenaCar = new Car { Name = "Ferrari Modena" };
            var fmcV = _database.Create.Vertex(ferrariModenaCar).Run();
            var bmwCar = new Car { Name = "BMW" };
            var bmwcV = _database.Create.Vertex(bmwCar).Run();
            var lp_fmcE = _database.Create.Edge("Owns").From(lpV.ORID).To(fmcV.ORID).Run();
            var lp_bmwcE = _database.Create.Edge("Owns").From(lpV.ORID).To(bmwcV.ORID).Run();

            var countryUS = new Country { Name = "US" };
            var uscV = _database.Create.Vertex(countryUS).Run();
            var lp_uscE = _database.Create.Edge("Lives").From(lpV.ORID).To(uscV.ORID).Run();

            var countryUK = new Country { Name = "UK" };
            var ukcV = _database.Create.Vertex(countryUK).Run();

            var pl = _database.Select().From<Person>().ToList<Person>().FirstOrDefault(p => p.Name == lukaPerson.Name);

            Assert.IsNotNull(pl);
            Assert.AreEqual(lukaPerson.Name, pl.Name);
            Assert.AreEqual(1, pl.out_Lives.Count);
            Assert.AreEqual(2, pl.out_Owns.Count);
        }
    }

    public class Person
    {
        public string Name { get; set; }
        public List<ORID> out_Lives { get; set; }
        public List<ORID> out_Owns { get; set; }
    }

    public class Country
    {
        public string Name { get; set; }
        public List<ORID> in_Lives { get; set; }
    }

    public class Car
    {
        public string Name { get; set; }
        public List<ORID> in_Owns { get; set; }
    }
}