neo4jclient C# and CALL {} IN TRANSACTIONS syntax

2022-01-07 15:54 zweistein imported from Stackoverflow

Using the C# neo4jclient how can I formulate the following cypher query (possible since neo4j 4.4)

MATCH (n:Foo) where n.foo='bar'
CALL { WITH n
DETACH DELETE n
} IN TRANSACTIONS OF 10000 ROWS;

Best regards

Andreas

2 answers

answered 2022-01-08 14:46 Lukasmp3
I suggest two options:
1. Use apoc procedure apoc.periodic.iterate
```
CALL apoc.periodic.iterate(
  "MATCH (n:Foo) WHERE n.foo='bar' RETURN n",
  "DETACH DELETE n",
  {batchSize: 10000}
);
```
More about https://neo4j.com/labs/apoc/4.2/overview/apoc.periodic/apoc.periodic.iterate/
1. Without apoc
  Run the following query in the while cycle as long as the return value is higher then zero.
```
MATCH (n:Foo)
WHERE n.foo='bar'
WITH n LIMIT 10000
DETACH DELETE n
RETURN count(*) AS numOfDeletedNodes
```
In both cases, you should have index on Foo.foo property or the query may take really a long time.
answered 2022-01-12 15:12 Charlotte Skardon
You need to get yourself version 4.1.20 of the client then you can use it like this:
```
client.Cypher
  .Match("(n:Foo)").Where("n.foo='bar'")
  .Call("{ WITH n DETACH DELETE n}")
  .InTransactions(10000);
```
It wasn't in 4.1.19 so you'll need the very latest version.

See also questions close to this topic

C# - Adding condition to func results in stack overflow exception
I have a func as part of specification class which sorts the given iqueryable
```
Func<IQueryable<T>, IOrderedQueryable<T>>? Sort { get; set; }
```
When i add more than one condition to the func like below , it results in stack overflow exception.
```
spec.OrderBy(sc => sc.Case.EndTime).OrderBy(sc => sc.Case.StartTime);
```
The OrderBy method is implemented like this
```
public ISpecification<T> OrderBy<TProperty>(Expression<Func<T, TProperty>> property)
    {
        _ = Sort == null ? Sort = items => items.OrderBy(property) : Sort = items => Sort(items).ThenBy(property);
        return this;
    }
```
Chaining or using separate lines doesn't make a difference.

This problem gets resolved if I assign a new instance of the specification and set it's func, but i don't want to be assigning to a new instance everytime. Please suggest what am i missing here and how to reuse the same instance (if possible).

How to projection fields for a dictionary (C#, MongdoDB)

I am trying my luck here, I have a model which is like the following

public class RowData : BaseBsonDefinition
{
.
  [BsonExtraElements]
  [BsonDictionaryOptions(DictionaryRepresentation.ArrayOfDocuments)]
  public Dictionary<string, object> Rows { get; set; } = new(StringComparer.OrdinalIgnoreCase);
.
}

In result, the schema in the MongoDB looks like

{
"_id": {
    "$binary": {
      "base64": "HiuI1sgyT0OZmcgGUit2dw==",
      "subType": "03"
    }
  },
  "c1": "AAA",
  "c8": "Fully Vac",
  "c10": "",
}

Those c1, c8 and c10 fields are keys from the dictionary, my question is how to dynamic project those fields?

I tried

Builders<RowData>.Projection.Exclude(p => "c1")

It seems the MongoDB driver can not handle a value directly.

Anyone could point me in the correct direction?

Thanks,

How do I add new DataSource to an already Databinded CheckBoxList

i'm building a web form that show Database's item(Tables, Rows, FK,...)

I have a CheckBoxList of Tables (chkListTable) which will show a new CheckBoxList of Rows (chkListRow) everytime I SelectedIndexChanged from chkListTable. The problem is i can show the items from chkListTable with 1 selected item. But i don't know how to show chkListRow if multiple item from chkListTable are selected.

Here are my codes:

aspx:

<div>
             <asp:Label ID="Label2" runat="server" Text="Table: "></asp:Label>
             <asp:CheckBoxList ID="chkListTable" runat="server"
                 DataTextField="name"
                 DataValueFeild="name"
                 AutoPostBack="true"
                 OnSelectedIndexChanged="chkListTable_SelectedIndexChanged">
             </asp:CheckBoxList>
        </div>
        <div>
             <asp:CheckBoxList ID="chkListRow" runat="server"
             DataTextField="COLUMN_NAME"
             DataValueField="COLUMN_NAME"
             RepeatDirection="Horizontal">
             </asp:CheckBoxList>
        </div>

aspx.cs:

protected void chkListTable_SelectedIndexChanged(object sender, EventArgs e)
    {
        tableName.Clear();
        
        foreach (ListItem item in chkListTable.Items)
        {
            if(item.Selected)
            {
                tableName.Add(item.Text.Trim());
            }    
        }

        for(int i = 0; i < tableName.Count; i++)
        {
            String query = "USE " + dbname +
                " SELECT * FROM information_schema.columns" +
                " WHERE table_name = '" + tableName[i] + "'" +
                " AND COLUMN_NAME != 'rowguid'";
            chkListRow.DataSource = Program.ExecSqlDataReader(query);
            chkListRow.DataBind(); 
            Program.conn.Close();
        }
    }

Program.cs:

public static bool Connect()
    {
        if (Program.conn != null && Program.conn.State == ConnectionState.Open)
            Program.conn.Close();
        try
        {
            Program.conn.ConnectionString = Program.constr;

            Program.conn.Open();

            return true;
        }

        catch (Exception e)
        {
            return false;
        }
    }

    public static SqlDataReader ExecSqlDataReader(String query)
    {
        SqlDataReader myreader;
        SqlCommand sqlcmd = new SqlCommand(query, Program.conn);
        sqlcmd.CommandType = CommandType.Text;
        if (Program.conn.State == ConnectionState.Closed) Program.conn.Open();
        try
        {
            myreader = sqlcmd.ExecuteReader();
            return myreader;
            myreader.Close();
        }
        catch (SqlException ex)
        {
            Program.conn.Close();
            return null;
        }
    }

I want my display to be like this:

[x]Table1 [x]Table2 [ ]Table3
[ ]Row1(Table1) [ ]Row2(Table1) [ ]Row3(Table1)
[ ]Row1(Table2) [ ]Row2(Table2)

Connection failure when using airflow to run python script connecting to neo4j database

I'm trying to use airflow to orchestrate a workflow where a neo4j docker is run and then a python script to query data from the neo4j database (the code runs on AWS EC2 instance). I am able to run the neo4j docker successfully. But when I ran the task of querying the database, I got connection error as:

neo4j.exceptions.ServiceUnavailable: Connection to 127.0.0.1:7687 closed without handshake response

If I manually ran the python script on EC2, it can connect to the neo4j database without any issue. The code I am using is as below:

class Neo4jConnection:

    def __init__(self, uri, user, pwd):
        self.__uri = uri
        self.__user = user
        self.__pwd = pwd
        self.__driver = None
        try:
            self.__driver = GraphDatabase.driver(self.__uri, auth=(self.__user, self.__pwd))
            logger.info('SUCCESS: Connected to the Neo4j Database.')
        except Exception as e:
            logger.info('ERROR: Could not connect to the Neo4j Database. See console for details.')
            raise SystemExit(e)

    def close(self):
        if self.__driver is not None:
            self.__driver.close()

    def query(self, query, parameters=None, db=None):
        assert self.__driver is not None, "Driver not initialized!"
        session = None
        response = None
        try:
            session = self.__driver.session(database=db) if db is not None else self.__driver.session()
            response = list(session.run(query, parameters))
        except Exception as e:
            logger.info("Query failed:", e)
        finally:
            if session is not None:
                session.close()
        return response 

class LoadWikiPathway2Neo4j:
    def __init__(self):
        # replace localhost with 127.0.0.1
        self.connection = Neo4jConnection(uri="bolt://localhost:7687",
                                          user="neo4j",
                                          pwd="test")
    def loadData(self):
        WIKIPATHWAY_MOUNTED_DATA_VOLUME = "/home/ec2-user/wikipathway_neo4j/data/Human/"  # the volume is mounted to neo4j docker
        WIKIPATHWAY_DATA_DOCKER_VOLUME = "file:///var/lib/neo4j/data/Human"  # file path in neo4j docker

        # connect db
        graph = self.connection
        # only run once
        graph.query('''MATCH (n) DETACH DELETE n''')
        graph.query('''CALL n10s.graphconfig.init()''')
        graph.query('''CREATE CONSTRAINT n10s_unique_uri IF NOT EXISTS ON (r:Resource) ASSERT r.uri IS UNIQUE''')
        graph.query('''call n10s.nsprefixes.removeAll()''')
        cypher = '''WITH '@prefix biopax:  <http://www.biopax.org/release/biopax-level3.owl#> . \
        @prefix cito:    <http://purl.org/spar/cito/> . \
        @prefix dc:      <http://purl.org/dc/elements/1.1/> . \
        @prefix dcat:     <http://www.w3.org/ns/dcat#> . \
        @prefix dcterms: <http://purl.org/dc/terms/> . \
        @prefix foaf:    <http://xmlns.com/foaf/0.1/> . \
        @prefix freq:    <http://purl.org/cld/freq/> . \
        @prefix gpml:    <http://vocabularies.wikipathways.org/gpml#> . \
        @prefix hmdb:     <https://identifiers.org/hmdb/> . \
        @prefix ncbigene: <https://identifiers.org/ncbigene/> .\
        @prefix owl:     <http://www.w3.org/2002/07/owl#> . \
        @prefix pav:     <http://purl.org/pav/> . \
        @prefix prov:    <http://www.w3.org/ns/prov#> . \
        @prefix pubmed:   <http://www.ncbi.nlm.nih.gov/pubmed/> .\
        @prefix rdf:     <http://www.w3.org/1999/02/22-rdf-syntax-ns#> . \
        @prefix rdfs:    <http://www.w3.org/2000/01/rdf-schema#> . \
        @prefix skos:    <http://www.w3.org/2004/02/skos/core#> . \
        @prefix void:    <http://rdfs.org/ns/void#> . \
        @prefix wp:      <http://vocabularies.wikipathways.org/wp#> . \
        @prefix wprdf:   <http://rdf.wikipathways.org/> . \
        @prefix xsd:     <http://www.w3.org/2001/XMLSchema#> . ' as txt \
        CALL n10s.nsprefixes.addFromText(txt) yield prefix, namespace RETURN prefix, namespace'''
        graph.query(cypher)

        fileLst = os.listdir(WIKIPATHWAY_MOUNTED_DATA_VOLUME)
        # load data
        for filename in fileLst:
            filePath = f'{WIKIPATHWAY_DATA_DOCKER_VOLUME}/{filename}'
            print(filePath)
            cypher = f'''CALL n10s.rdf.import.fetch("{filePath}","Turtle")'''
            logger.info(cypher)
            graph.query(cypher)
            logger.info(f"{filename} is loaded")
        graph.close()

def load_wikipathway_files():
    data = LoadWikiPathway2Neo4j()
    data.loadData()

with DAG(
        default_args=default,
        dag_id=os.path.basename(__file__).replace(".py", ""),
        schedule_interval=None,
        catchup=False,
        start_date=days_ago(1),
        tags=['s3', 'download_wikipathway'],
) as dag:
     loadWikipathwayFile = PythonOperator(
        task_id="load_wikipathway",
        python_callable=load_wikipathway_files
    )

Return count of relationships instead of all
I wonder if anyone can advise how to adjust the following query so that it returns one relationship with a count of the number of actual relationships rather than every relationship? I have some nodes with many relationships and it's killing the graph's performance.
```
MATCH (p:Provider{countorig: "XXXX"})-[r:supplied]-(i:Importer)
RETURN p,  i limit 100
```
Many thanks

How to extract dynamic property names from a json file in Neo4J using Cypher Query

The tags property names are dynamic. e.g. linux and cypher are dynamic in the json. I am trying to extract the dynamic tags and their values and associate them as properties to the Person node. Here is what I have so far:

CALL apoc.load.json("file:///example.json")
YIELD value
MERGE (p:Person {name: value.name})
ON CREATE SET 
p.job = value.job,
p.department = value.department

RETURN p as Person;

example.json:

{
    "name": "Harry",
    "job": "Developer",
    "tags": {
        "linux": "awesome",
        "cypher": "Working on it"
    },

    "department": "IT"

}

how to ensure atomicity in neo4j cypher writes:

I have never used the transaction lock features in neo4j cypher before so I not sure how to do this. I have a series of writes that I need all to complete or none complete:

CREATE (e:Elections) SET e +=$electionObj,
                             e.election_id=apoc.create.uuid(),e.createdAt=date()

                            WITH e, $nominate_authorizers AS na
                            UNWIND na AS n_auth 
                            MATCH (nm:Member {member_id: n_auth.member_id})
                            CREATE (nm)-[nr:NOMINATE_AUTHORIZER]->(e)

                            WITH e, nm, $certify_authorizers AS ca
                            UNWIND ca AS c_auth 
                            MATCH (cm:Member {member_id: c_auth.member_id})
                            CREATE (cm)-[cr:CERTIFY_AUTHORIZER]->(e)

                            WITH e, nm,cm, $tally_authorizers AS ta
                            UNWIND ta AS t_auth 
                            MATCH (tm:Member {member_id: t_auth.member_id})
                            CREATE (tm)-[tr:TALLY_AUTHORIZER]->(e)

                            WITH e, nm,cm, tm, $audit_authorizers AS aa
                            UNWIND aa AS a_auth 
                            MATCH (am:Member {member_id: a_auth.member_id})
                            CREATE (am)-[ar:AUDIT_AUTHORIZER]->(e)

                            WITH e, nm,cm, tm, am, $races AS races
                            UNWIND races AS race 
                            CREATE (r:Race) 
                            SET r.name= race, 
                            r.race_id=apoc.create.uuid()
                            CREATE (r)-[rr:OF_ELECTION]->(e) 
                            
                            RETURN {election: e}

The problem is ...if there's an error that causes one of these MATCH's to return 0 row the query moves right along and creates the nodes it found. What is the most efficient of achieving atomicity here. I have looked at the apoc library but not sure about it....any suggestion would be appreciated.

C# neo4j - Parameter maps cannot be used in MERGE patterns (use a literal map instead
I want to create relationship between nodes with parameter as an object like -
```
               await _graphClient.ConnectAsync();
               await _graphClient.Cypher
               .Match("(obj1:Client)")
               .Where((Client obj1) => obj1.ClientId == UserId)
               .Match("(obj2:Job)")
               .Where((Job obj2) => obj2.Id == JobId) 
               .Merge("(obj1)-[r:JOB_POSTED $relationobj1]->(obj2)")
               .WithParam("relationobj1", relationobj1)
               .ExecuteWithoutResultsAsync();
```
Above code is giving error -

{"Parameter maps cannot be used in MERGE patterns (use a literal map instead, eg. "{id: {param}.id}") (line 5, column 29 (offset: 130))\n"MERGE (obj1)-[r:JOB_POSTED $relationobj1]->(obj2)"\n

If I remove $relationobj1 from relation param, its working.

Object relationobj1 is generated like this -
```
JOBS_POSTED relationobj1 = new JOBS_POSTED();
relationobj1.createddate = DateTime.UtcNow;
```
Any idea on what's the correct way to pass parameter as an object?

Thanks in advance
How can I get started with Neo4jClient?

As the wiki docs are currently out of date, where can I get started? I have tried to get started with the out-of-date docs, but get some problems with the Async equivalents.

Get error when run cypher query multi line in php

I get this error while that run this query cypher from luadis php driver Single line query runed but multi line query cypher doesn't run

$hosturl =  self::$config['hosturl'];
$result = $client->run("
            UNWIND range(0,4) as level
            CALL apoc.cypher.doIt('
            MATCH (c:Category { pagesFetched: false, level: $level })
            CALL apoc.load.json('$hosturl/w/api.php?format=json&action=query&list=categorymembers&cmtype=page&cmtitle=Category:' + apoc.text.urlencode(c.catName) + '&cmprop=ids|title&cmlimit=500')
            YIELD value as results
            UNWIND results.query.categorymembers AS page
            MERGE (p:Page {pageId: page.pageid})
            ON CREATE SET p.pageTitle = page.title, p.pageUrl = '$hosturl/wiki/' + apoc.text.urlencode(replace(page.title, ' ', '_'))
            WITH p,c
            MERGE (p)-[:IN_CATEGORY]->(c)
            WITH DISTINCT c
            SET c.pagesFetched = true', { level: level }) yield value
            return value");

And i get this error :

[Sat Feb 19 00:27:39 2022] PHP Fatal error:  Uncaught Bolt\error\MessageException: Invalid input 'h': expected whitespace, '.', node labels or rel types, '[', '^', '*', '/', '%', '+', '-', "=~", IN, STARTS, ENDS, CONTAINS, IS, '=', "<>", "!=", '<', '>', "<=", ">=", AND, XOR, OR, ',' or ')' (line 5,
column 25 (offset: 141))
"                       CALL apoc.load.json('https://en.wikipedia.org/w/api.php?format=json&action=query&list=categorymembers&cmtype=page&cmtitle=Category:' + apoc.text.urlencode(c.catName) + '&cmprop=ids|title&cmlimit=500')"
                         ^ (Neo.ClientError.Statement.SyntaxError) in C:\xampp\htdocs\Graph\vendor\stefanak-michal\bolt\src\protocol\V3.php:82
Stack trace:
#0 C:\xampp\htdocs\Graph\vendor\laudis\neo4j-php-client\src\Bolt\BoltUnmanagedTransaction.php(131): Bolt\protocol\V3->run()
#1 C:\xampp\htdocs\Graph\vendor\laudis\neo4j-php-client\src\Bolt\Session.php(87): Laudis\Neo4j\Bolt\BoltUnmanagedTransaction->runStatements()
#2 C:\xampp\htdocs\Graph\vendor\laudis\neo4j-php-client\src\Client.php(77): Laudis\Neo4j\Bolt\Session->runStatements in C:\xampp\htdocs\Graph\vendor\laudis\neo4j-php-client\src\Exception\Neo4jException.php on line 50
[Sat Feb 19 00:27:39 2022] [::1]:1194 [500]: GET /querycypher.php - Uncaught Bolt\error\MessageException: Invalid input 'h': expected whitespace, '.', node labels or rel types, '[', '^', '*', '/', '%', '+', '-', "=~", IN, STARTS, ENDS, CONTAINS, IS, '=', "<>", "!=", '<', '>', "<=", ">=", AND, XOR, O
R, ',' or ')' (line 5, column 25 (offset: 141))
"                       CALL apoc.load.json('https://en.wikipedia.org/w/api.php?format=json&action=query&list=categorymembers&cmtype=page&cmtitle=Category:' + apoc.text.urlencode(c.catName) + '&cmprop=ids|title&cmlimit=500')"
                         ^ (Neo.ClientError.Statement.SyntaxError) in C:\xampp\htdocs\Graph\vendor\stefanak-michal\bolt\src\protocol\V3.php:82
Stack trace:
#0 C:\xampp\htdocs\Graph\vendor\laudis\neo4j-php-client\src\Bolt\BoltUnmanagedTransaction.php(131): Bolt\protocol\V3->run()
#1 C:\xampp\htdocs\Graph\vendor\laudis\neo4j-php-client\src\Bolt\Session.php(87): Laudis\Neo4j\Bolt\BoltUnmanagedTransaction->runStatements()
#2 C:\xampp\htdocs\Graph\vendor\laudis\neo4j-php-client\src\Client.php(77): Laudis\Neo4j\Bolt\Session->runStatements in C:\xampp\htdocs\Graph\vendor\laudis\neo4j-php-client\src\Exception\Neo4jException.php on line 50
[Sat Feb 19 00:27:39 2022] [::1]:1194 Closing

Please say to me how to solve this Please help me

Related questions

neo4jclient C# and CALL {} IN TRANSACTIONS syntax

2 answers

See also questions close to this topic