Consider support for SQL Server 2017's graph processing capabilities

[divega]

More details in https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/20/graph-data-processing-with-sql-server-2017/.

Customer feedback provided at https://blogs.msdn.microsoft.com/dotnet/2017/05/12/announcing-ef-core-2-0-preview-1/#comment-285545.

[jvveldhuizen]

It's a very interesting feature. I looked into http://neo4j.com before and these great features are now added to SQL. Server 2017,. What I really like is that you can define relations like John --> friends --> Peter Peter --> friends --> John John --> nephew --> Mary Mary --> Niece --> John

So the relation between persons can differ per direction! And you can add a lot of relations between nodes. I think the combination with relational databases and a graph database like in SQL 2017 sounds brilliant.

[masha6]

Any plans/roadmap/timeline for this important feature?

[sameercarpenter]

I am eagerly waiting for this feature to be added on EF Core.

[cSchluetter]

Do you have any news on this topic? I am very interested in using EF with the graph capabilities of SQL Server 2017.

[WiredUK]

I think this is a really interesting feature but not sure how it would work with EF. Perhaps we could discuss it here?

1. Annotations for migrations: This should be a fairly simple matter of adding method(s) similar to ForSqlServerIsMemoryOptimized (e.g. ForSqlServerIsGraphNode and ForSqlServerIsGraphEdge)

2. How to materialise the $node_id, $edge_id, $from_id and $to_id columns? Would we even want these? Perhaps we could have annotations against entity properties to identify them?

3. What would be the syntax to add a relation? For example (assuming nodes of people with edge table containing Facebook style likes):

   var from = context.Persons.Single(x => x.Id = 1); var to = context.Persons.Single(x => x.Id = 2);

   // On context? context.AddRelation(from, destination); context.AddRelation(new Like { LikeType = LikeTypeEnum.Angry }, from, to);

   // On the Like DbSet? Maybe we have an EdgeDbSet? context.Likes.AddRelation(from, to); context.Likes.AddRelation(new Like { LikeType = LikeTypeEnum.Angry }, from, to);

   // Perhaps edge type can inherit from an edge base class: var like = new Like { LikeType = LikeTypeEnum.Angry }; context.Likes.Add(like.AsRelation(from, to));

   // Or perhaps node entities need to implement an interface and edge entities have a // constructor that takes a form and to, both of type INode var like = new Like(from, to) { LikeType = LikeTypeEnum.Angry };

4. Edge tables don't need a primary key to function, EF requires all tables to have one. Do we just impose a rule that they have to have one or do they become a 'special' type? It's obviously going to a lot simpler to implement the former.

5. How do we implement the MATCH function? I think this is where things get weird for querying. A standard graph query looks like this. This one will tell us all the people that are liked by Bob. Note how the FROM has 3 tables and one is aliased.

   SELECT Person2.Name FROM Persons, Likes, Persons AS Persons2 WHERE MATCH(Persons-(Likes)->Persons2) AND Persons.Name = 'Bob'

   Or how about the people that Bob likes that also like him? Note how you can traverse the graph with as many hops as you like:

   SELECT Person2.Name FROM Persons, Likes, Persons AS Persons2, Persons AS Persons3 WHERE MATCH(Persons-(Likes)->Persons2-(Likes)->Persons3) AND Persons.Name = 'Bob' AND Persons3.Name = 'Bob'

   And you can also have multiple MATCH clauses joined with AND:

   SELECT Person2.Name FROM Persons, Likes, Persons AS Persons2, LivesIn, Cities WHERE MATCH(Persons-(Likes)->Persons2 AND Persons-(LivesIn)->Cities) AND Persons.Name = 'Bob'

   How would we model that in Linq?

The first 4 items are pretty trivial, I reckon I could make a PR for that in a couple of days. The last item does require some creative thinking. Did I miss anything else?

Fun feature though!

[WiredUK]

Been playing around with this code and now have a working version of EF Core that supports much of the graph processing feature.

So you can do this to configure the model:

modelBuilder.Entity<Person>(entity ->
{
    entity.ForSqlServerIsGraphNode(e => e.NodeId);
};
modelBuilder.Entity<Friend>(entity ->
{
    entity.ForSqlServerIsGraphEdge(e => e.FromId, e => e.ToId);
};

This sets up the annotations required and also allows you to run dotnet ef database update to create the tables correctly.

The psuedocolumns (e.g. $node_id, $from_id and $to_id) are being set up by passing the property expressions into the ForSqlServerIsGraph* methods.

Querying the graph is simpler than I thought it was going to be as all MATCH clauses can be simplified. For example

MATCH(Persons1-(Likes1)->Persons2-(Likes2)->Persons3)

Is exactly the same as

MATCH(Persons1-(Likes1)->Persons2)
AND MATCH(Persons2-(Likes2)->Persons3)

Now I can write Linq queries like this and get good SQL:

var friendsQuery = from person1 in _context.People
                   from friend1 in _context.Friends
                   from person2 in _context.People
                   from friend2 in _context.Friends
                   from person3 in _context.People
                   where EF.Functions.Match(person1, friend1, person2)
                   && EF.Functions.Match(person2, friend2, person3)
                   select person1;

INSERT queries work well as does UPDATE

All tests pass and though I've added some, I still need to add more tests for my additional code.

This has been a really fun exercise and though I don't expect a PR to be accepted it would be super satisfying. Obviously I don't want to dump a large PR on the team, so what would be the best way to proceed?

By the way, rooting around the internals of EF Core has given me a good respect for the EF team, it's really impressive!

[ajcvickers]

@WiredUK Thanks for spending the time looking at this. @divega is going to take an initial look at your approach and provide some feedback, and we can take it from there.

[divega]

@WiredUK I have just finished reviewing your posts and getting a refresh of the graph processing feature in SQL Server (it has been a while since I last looked at it).

What you describe in your second post seems indeed very compelling! We would really appreciate that you submit a PR to consider including the feature in the EF Core provider for SQL Server.

I have a few questions, but nothing that we cannot discuss in more detail over the PR. For example:

1. Are the pseudo-columns mapped to shadow properties? Are there scenarios in which it could make sense to the user to map them to actual properties on the objects?

2. Did you have to give INSERTs on the edge table any special treatment? Last time I looked at SQL-based examples, I saw a pattern that inserts edges using sub-queries to get the $node_id values from the node tables using sub-queries:

   INSERT INTO dbo.Likes (
      $from_id, $to_id) VALUES (
      (SELECT $node_id FROM dbo.Person WHERE ID = 1),
      (SELECT $node_id FROM dbo.Dog WHERE ID = 1));

   And it wasn't clear to me if this was really required or if just setting up $node_id as alternate keys and relying on foreign key fix-up would be enough.

I should also mention that if you send a PR at this stage, it is very possible that we won't take it for 2.2 (we are already working on stabilizing this release, so no new features are getting added) but it seems reasonable to have it for the first preview of 3.0.

Looking forward to go through the PR process with you.

PS: in case you haven't seen it, there is a new enhancement in SQL Server 2019 that may be relevant to EF Core: graph edge constraints.

[ajcvickers]

Moving this back onto the backlog for tracking, since the EF team is not working on this right now. However, that certainly doesn't preclude a community PR as described by @divega above.

[bvdwalt]

Any idea as to when this might be considered by the EF team or we can just use this to collaborate on getting a new PR going for us wanting to use the graph functionality on SQL Server? @WiredUK 's PR has been closed due to there being too many changes in the latest major release of EF.

[ajcvickers]

@bvdwalt I doubt this is something that the EF team will implement any time soon. However, we will consider a well-written PR that integrates with the query changes made in 3.0.

[jcolon1984]

Oh my god... there no ORM for graph database and it's been 3 years since it is supported by SQL Server 2017?

[jcolon1984]

@divega can you bring it up to your product owner?

[heart2friend]

Any idea when this feature will be added? I am eagerly waiting for it. Is there any alternate for this in ef core?

[ajcvickers]

@heart2friend This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 5.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. This issue currently has 28 votes (👍) which puts it at position 29 in the list of most voted features.

[Chrislock1]

I'm working for a company that thinks this would be a very useful feature, so I have been tasked with attempting an implementation. I'm currently working on a branch for this that branches from the 5.0 release.

I'm aiming for a graph feature in EF Core more than SQL Graph support though. I am making a graph module in the InMemoryDatabase as well as an implementation for SQL Graph. So far I'm able to both do inserts and query graph data, but I'm still far from actually done.

My current implementation is inspired by the PR made by @WiredUK, but I'm trying to make something that is provider agnostic and provides edges as a new entity feature, comparable to entities. An edge is basically a skip table with more properties that connects any pair of entity types as long as no constraints are defined.

My implementation is based on a Edge type that basically looks like this:

public class Edge
{
    public int EdgeId { get; set; }
    public int FromId { get; set; }
    public int ToId { get; set; }

    public Type EdgeType { get; set; }
    public Type FromType { get; set; }
    public Type ToType { get; set; }

}

This allows you to do very rich queries:

// Get all edges originating from a node
IEnumerable<Edge> getAllEdges = _context.Set<NodeEntity>().SelectMany(node => EF.Functions.GetEdges(node));

// Get all edges of a specific entity type on a node
IEnumerable<EdgeEntity> getEdgesOfType = _context.Set<NodeEntity>().SelectMany(node => EF.Functions.GetEdges<EdgeEntity>(node));

// All edges regardless of type between two nodes
IEnumerable<Edge> allConnections = _context.Edges.Between(fromNode, toNode);

// Resolve an entity from an edge
Edge coreEdge;
/*
      ...
*/
EdgeEntity edge = _context.Edges.GetEntity<EdgeEntity>(coreEdge);

And the MATCH implementation looks pretty much exactly like @WiredUK described it.

What do you think about such an implementation? Is this something the team would consider a PR on?

[ajcvickers]

/cc @smitpatel

[smitpatel]

@Chrislock1 - Thank you for the proposal. I looked at proposal and SqlServer graph capabilities. In your example you are using Edge/EdgeEntity both. From Sqlserver perspective there is a table which is marked as node and another table which is marked as edge to store them. This differs and/or add additional client side processing from your proposal. Further, in order to do it provider agnostic way, we need to compare such features in MySql/npgsql/Sqlite database if they have and find a common abstraction. If you are willing to do that then feel free to research those database and make a proposal. In-Memory is not a relational database and it does not act like one. While feature could be made to behave similarly for In-Memory and SqlServer, they are fundamentally different implementation and does not define an abstraction to call it provider agnostic. It gives false sense to customers that In-Memory can be used for testing an app which uses SqlServer in prod. Further, adding such an implementation in In-Memory which functionally behaves the same as SqlServer could be harmful if other relational databases have a different graph processing structure. We would want to avoid anything in In-Memory database unless it is either available in most relational database or it is a well-defined feature of In-Memory which does not have functionality similarity to a particular kind of database.

Feel free to submit provider agnostic proposal which works on different relational database or if you wish to clarify your proposal above. We would like to see a well-defined design proposal before you submit a PR to avoid unnecessary work on your part if the design is not something we can implement in EF Core.

[ohault]

Hi @Chrislock1, any update since last year ? More generally, EF Core should support a way to leverage structural data representation like trees (e.g. using under the cover nestedset), graphs, ...

[Shahid-Roofi-Khan]

i'd highly suggest EFCORE should leverage the graph database capabilities of SQL 2017 to handle relations? Though it changes how RDBMS always handled relations in past

[sajjadarashhh]

who know when we can use graphdatabase features in efCore?

[ajcvickers]

@sajjadarashhh This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 7.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to vote (👍) for this issue if it is important to you.

[ohault]

Hi @ajcvickers , graph support in EF Core is a strategic feature.

[ajcvickers]

@ohault We consider that in the release planning process.

[ohault]

Great, I just hope graph support will be validated as a Themes for EF Core 7.X ou 8.0 at a next meeting.

[wstaelens]

I've been searching for a while now. Looking for a simple graph database. Easy to maintain, works with .net c#, if possible a triplestore / rdf.

So far I came to the conclusion that Microsoft only has Cosmos DB gremlin api for this. But this costs a lot of money and doesn't support anything like dotnetrdf. The tools to visualize the graph (when not logging in to the azure portal) are very expensive. Design tools like Hackolade are also expensive (too limited for community edition).

Other solutions like Virtuoso, ontotext graphdb etc... are expensive (or require a lot of changes in terms of hosting, maintenance, support, etc...).

It would be really AWESOME if we could leverage the Graph features in SQL server from EF. Maybe as a simple triplestore / rdf and linking it up with the dotnetrdf project. That would really close a big gap in the .net eco system. SQL Express supports Graph (according the documentation) so this would make it very accessible to all users. Think about semantic web, rdf, json-ld, small knowlegde graphs, ...

Hopefully we will see this very soon in release 7.1.x

[roji]

@wstaelens there's not going to be a 7.1 release - the next feature release is 8.0, where Graph database support isn't currently on the plan (due to higher-requested features having priority).

[wstaelens]

maybe @Chrislock1 has already an early implementation? wishful :-)

[sstoyanov7]

We have also need EF to support either SQL Server graph DB or Neo4J. It's pitty it's not implmeneted yet..

1. What lib does everyone use as substiute atm?
2. @Microsoft any update on considering EF support for graph db?

[jcolon1984]

Its been 5 years? Big backlog yall got there.

[roji]

Big backlog yall got there.

@jcolon1984 indeed we do. Although this issue does have votes, it isn't e.g. in the top 10 of highly-voted issues. In addition, this is a SQL Server-only feature that would require quite a bit of design and implementation effort; we have a general tenedency to prefer work on general features that benefits users of all databases.

[jlchavez]

QL Server-only feature that would require quite a bit of design and implementation effort; we have a general tenedency to prefer work on general features that benefits users of all databases.

Any recommendation on a third party product that would allow us to use the Graph feature in Microsoft SQL Server, since your ORM doesn't support it after 5 years.

[roji]

@jlchavez you can use SQL querying when needing to interact with SQL Server's graph features; EF Core can still be used to materialize the results to entities - see the docs.

[popcatalin81]

Official support for this would be nice. I've been trying workarounds of a couple of days now, and I would really like to avoid going to SQL queries.

[jcolon1984]

@jlchavez you can use SQL querying when needing to interact with SQL Server's graph features; EF Core can still be used to materialize the results to entities - see the docs.

5 years ago when I had to solve this issue, I recommended the project team to move to neo4j and drop MSSQL support for graph databases use cases since using SQL server and ORM like efcore seemed to be on its infancy stages for graphs. As a .net developer it would be nice if I could recommend MSSQL/efcore combo in the future but it seems to be in the same place it was 5 years ago.

[Ozzian]

I'm considering extending the current monolithic database with graph capabilities, and found my self here. I also toss a coin to the wishing well to get the support from EFCore.

[ohault]

Hi @ajcvickers Arthur, in follow-up to your Build 2024 session about EF.core, can you please update this tickets according support of graph databases by EF Core (extremely important for AI) ?

I have just created a dedicated feature request for this according to importance.

[roji]

@ohault I'm not sure exactly what sort of update you're looking for (or how it relates to the Build 2024 session). Graph support for SQL Server (or for any other database) isn't in the plan for EF Core 9.0 - we'll evaluate it for future versions. For now, this feature is in the backlog, and has quite a few issues that are more highly-voted by our users.

As I wrote in #33795, please provide context on why you consider SQL Server graph support as "extremely important for AI", that may help prioritize work on this.