PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Why does the value of a method doesn't change inside select method?

I have a method that return value from sequence in Postgres:

public long GetSId(DatabaseFacade d, string sequenceName)
{
    var result = new NpgsqlParameter(":result", NpgsqlDbType.Integer)
    {
        Direction = System.Data.ParameterDirection.InputOutput,
        Value = 0
    };
    d.ExecuteSqlRaw($"SELECT nextval('{sequenceName}')", result);
    return (long)result.Value;
}

And I have a method to form data:

private List<CrossingsResult> FormCrossingResult()
{
    var d = _dbContext.Database;
    var crosswalks = _dbContext.Crosswalks
        .Select(x => new CrossingsResult
        {
            SId = GetSId(d, _sequenceName),
            ...
        }).ToList();
    return crosswalks;

The problem is when I call the method inside Select the SId doesn’t change (for example, it will put for all records SId same value). But if I call the method outside crosswalk it works
Like here:

private List<CrossingsResult> FormCrossingResult()
{
    var d = _dbContext.Database;
    Console.WriteLine($"First call: {GetSId(d, _sequenceName)}");
    Console.WriteLine($"Second call: {GetSId(d, _sequenceName)}");
    Console.WriteLine($"Third call: {GetSId(d, _sequenceName)}");
    var crosswalks = _dbContext.Crosswalks
        .Select(x => new CrossingsResult
        {
            SId = GetSId(d, _sequenceName),
            ...
        }).ToList();
    foreach (var crosswalk in crosswalks)
    {
        Console.WriteLine(crosswalk.SId);
    }
    return crosswalks;

Result be like this:
Console output

I tried to make it with for loop and it works:

private List<CrossingsResult> FormCrossingResult()
{
    var d = _dbContext.Database;
    var crosswalks = _dbContext.Crosswalks
        .Select(x => new CrossingsResult
        {
            ...
        }).ToList();
    foreach (var crosswalk in crosswalks)
    {
        crosswalk.SId = GetSId(d, _sequenceName);
    }
    return crosswalks;

But I want the method called inside Select and to make it without using for loop

Tags:

2

Answers


  1. 0

    You are using nextval in a wrong way…
    Technically, the argument passed to the nextval() function is actually its OID from the pg_class system catalog view. When you pass the sequence’s name, Postgres looks up its OID and uses that. (in your case it will return 123 from that point forward until new row will be created).

    You can use the nextval() function when inserting data into a table. This allows you to have a column that contains sequential values across all rows.

    Login or Signup to reply.
  2. 0

    First of all I would start from that this looks suspiciously close to being a The XY Problem.

    As for what is happening here. I can’t pinpoint at the moment why exactly (i.e. docs/source code) it behaves like that but the reason is basically is the following – EF Core will translate your LINQ code into SQL and GetSId is obviously is not a translatable function from EF Core point of view (it is just some "random" user-defined one). But EF Core is a bit smart and it can handle some untranslatable parts in the final projection (i.e. Select in this case). If you will add a side effect to the function:

    public long GetSId(DatabaseFacade d, string sequenceName)
{
   Console.WriteLine($"get seq: {sequenceName}");
   // ...
}

    You will see that the function is called only a single time. This is caused by the fact that function is not actually using any data from the processed entity (and it is the part I can’t explain with docs/source code). If you change the function to something like (for demonstration purposes):

    public long GetSId(object dummyData, DatabaseFacade d, string sequenceName)
{
    Console.WriteLine($"get seq: {sequenceName}");
    return Random.Shared.Next();
}

    And will change the call to:

    var crosswalks = _dbContext.Crosswalks
    .Select(x => new CrossingsResult
    {
        SId = GetSId(x.SomeProp // dummy prop to change translation
            , d
            , _sequenceName),
        ...
    })
    .ToList();

    You will see that GetSId will be invoked several times as expected. But actually calling database from the same context will fail since you will already have an operation running on it.

    As a quick fix you can go with the foreach approach you have but in general I would recommend to consider some other approach, for example defining a stored procedure to fetch all the data or maybe using EF Core ability to provide user-defined function mapping.

    See also:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search