Nhibernate Formula equivalent

[marcmognol]

Issue

This issue is a Feature request.
We have some project where we use NHibernate as ORM and we started to move to EF core.

With NHibernate we had a Formula extension which were use to map a property to a stored function.

I miss this one very much

Motivation

Make SQL queries with lot of functions call easier and smaller

Example

To generate the following SQL query :

SELECT order_no, line_no, part_no, Inventory_Part_Api.Get_Description(part_no) as DESCRIPTION
FROM CustomerOrderLine

With NHibernate

Model is:

public CustomerOrderLine()
{
  public int OrderNo {get; set; }
  public int LineNo {get; set; }
  public string PartNo {get; set; }
  public string Description { get; set; }
}

Mapping is:

public CustomerOrderLineMap()
{
    Table("CUSTOMER_ORDER_LINE");

    CompositeId().KeyProperty(x => x.OrderNo, "ORDER_NO")
                 .KeyProperty(x => x.LineNo, "LINE_NO");

    Map(x => x.PartNo).Column("PART_NO");
    Map(x => x.Description).Formula("Inventory_Part_Api.Get_Description(part_no)");
}

To query:
var data = _session.Get<CustomerOrderLine>(new CustomerOrderLine() { OrderNo = 1234, LineNo = 1 });

With EF core

Model is:

public CustomerOrderLine()
{
  [Column("ORDER_NO")]
  public int OrderNo {get; set; }

  [Column("LINE_NO")]
  public int LineNo {get; set; }

  [Column("PART_NO")]
  public string PartNo {get; set; }

  [NotMapped]
  public string Description { get; set; }
}

To query:

var data = _dbContext.CustomerOrderLines.Select(x => new CustomerOrderLine
                                                        {
                                                            OrderNo = x.OrderNo,
                                                            LineNo = x.LineNo,
                                                            PartNo = x.PartNo,
                                                            Description = DbContext.MyFunction(x.PartNo)
                                                        });,

Problems

As you can see in the above example, the code to query the data against the DB is much complicated because I have to make a projection in the Select, means that for ONE "property" mapped to a formula, I have to list all other properties in the Select projection.

And sometimes, the formula is in a sub-sub-child entity, means I have to build a Select projection very very long.

Maybe there is a workaround to make a lighter Select projection, any suggestion appreciated ?

Suggestion

Could be awesome to have the same Formula behaviour in EF core.
Property could be available only on SELECT statement.

Having an attribute like:

public CustomerOrderLine()
{
  [Column("ORDER_NO")]
  public int OrderNo {get; set; }

  [Column("LINE_NO")]
  public int LineNo {get; set; }

  [Column("PART_NO")]
  public string PartNo {get; set; }

  [DbFormula(Schema = "", Package = "Inventory_Part_Api", Function = ".Get_Description(part_no)"]
  public string Description { get; set; }
}

Conclusion

Thank you to help me on this, making my code better to read 👍

[ErikEJ]

Which database provider?

[marcmognol]

I am using Oracle.EntityFrameworkCore provider.

[AndriySvyryd]

@Heisenberg74 We do have computed columns support. Is there anything about it that doesn't satisfy your requirements apart from not having a way of configuring it with a data annotation?

modelBuilder.Entity<CustomerOrderLine>()
    .Property(p => p.Description)
    .HasComputedColumnSql("Inventory_Part_Api.Get_Description(part_no)");

[marcmognol]

@AndriySvyryd Thank you for your answer.
I try with your suggestion but it doesn't work.
Ths generated SQL query doesn't replace the field with string provided by the HasComputedColumnSql()

SELECT "s"."ORDER_NO", "s"."RELEASE_NO", "s"."SEQUENCE_NO", "s"."CONTRACT", "s"."PART_NO", "s0"."Description"
FROM "IFSAPP"."CUSTOMER_ORDER_LINE" "s"

Any idea?
Is it related to oracle provider?

Thx

[ajcvickers]

This seems like a duplicate of #10768.

[marcmognol]

This seems like a duplicate of #10768.

Correct !

[marcmognol]

But I don't see the right way to achieve my query.

[ajcvickers]

@Heisenberg74 This is not yet supported by EF Core. Make sure to vote (👍) for #10768 since we use votes to help decide what to work on and #10768 currently has no votes.

[marcmognol]

Thank you for your time. Hope one day this feature will come because the ERP on which I work, uses a lot of stored functions.

And I prefer not revert to NHibernate...