Linq to SQL column computed from query to different table?

Question

Is it possible to define a column in one of my Linq to SQL entities such that it's calculated as part of the SQL query?

Here is my Entity class so far:

[Table(Name = "Products")]
public class ProductEntity
{
    [Column(Name = "ModelNumber", IsPrimaryKey = true)]
    public string ModelNumber { get; set; }

    [Column(Name = "SerialNumber", IsPrimaryKey = true)]
    public string SerialNumber { get; set; }
}

I want to add a new property like:

[Column]
public bool ActiveListing { get; set; }

Except its value should be calculated based on a complicated Linq query which I am executing elsewhere right now (not in SQL!):

context.GetTable<SalesRecords>().
    Where(ah => ah.Date <= GlobalCoordinatedDateTime.Local).
    Where(ah => ah.ProductModelNumber == ModelNumber && ah.ProductSerialNumber == SerialNumber).
    OrderByDescending(ah => ah.Date).
    FirstOrDefault().Status == Statuses.Active;

My best guess is that I could use AssociationAttribute but I don't know how to do that when there could be more than one match in the "SalesRecords" table and I want to get the first after applying some ordering.

EDIT: My question is not the same as a simple "computed column" as seen in this question, I need to compute the value using data stored in different tables, outside of the entity its self.

To me the entity classes should simply reflect the table that they are a mapping of. You could create a database view in sql that does what you do in code and create a new class that is a mapping of the view. — mortb, Mar 20 '19 at 16:06
Are you really need a `set` method of `ActiveListing` property? — Aleks Andreev, Mar 20 '19 at 16:08
It is EF, not linq, but [this could be helpful](https://stackoverflow.com/questions/15585330/calculated-column-in-ef-code-first) another [EF one](https://www.davepaquette.com/archive/2012/09/23/calculated-columns-in-entity-framework-code-first-migrations.aspx) — Cleptus, Mar 20 '19 at 16:13
Your calculation will raise a `NullReferenceException` when there are no rows matching in `SalesRecords` table. — Cleptus, Mar 20 '19 at 16:16
Possible duplicate of [how to map a computed column in linq to sql classes](https://stackoverflow.com/questions/34938821/how-to-map-a-computed-column-in-linq-to-sql-classes) — Cleptus, Mar 20 '19 at 16:18
@bradbury9 Clarified how my question is not the same in my OP. — Kyle V., Mar 20 '19 at 16:50
@bradbury9 I don't think I can really apply those EF references since I have no control over how this database I'm consuming is constructed. — Kyle V., Mar 20 '19 at 16:57
Couldn't you use the `Column` attribute `Expression` property to set a SQL expression that represents what you would use in a SQL `SELECT` statement? — NetMage, Mar 20 '19 at 19:14

Oscar David Diaz Fortaleché · Answer 1 · 2019-03-20T19:03:44.640

if you don't need to store the column at database you can use:

[Table(Name = "Products")]
public class ProductEntity
{
    // your other columns...

    [NotMapped]
    public bool ActiveListing {
        get
        {
            bool result = false;

            // your logic to calculate then set to "result" variable

            return result;
        }
    }

}

but if you need to store it, change the name of ActiveListing property, then manually assign to the final ActiveListing property before you will create or update the record. Example:

[Table(Name = "Products")]
public class ProductEntity
{
    // your other columns...

    [NotMapped]
    public bool CalculateActiveListing
    {
        get
        {
            bool result = false;

            // your logic to calculate then set to "result" variable

            return result;
        }
    }

    public bool ActiveListing { get; set; }
}

here an example if you have a navigation property to SalesRecords. important have Lazy Loading enabled, or use the Include() method.

    [NotMapped]
    public bool CalculateActiveListing
    {
        get
        {
            bool result = false;

            // your logic to calculate then set to "result" variable.
            // for example:

            // validate SalesRecords has data
            if (this.SalesRecords != null)
            {
                var sale = this.SalesRecords
                    .Where(ah => ah.Date <= GlobalCoordinatedDateTime.Local)
                    .Where(ah => ah.ProductModelNumber == ModelNumber && ah.ProductSerialNumber == SerialNumber)
                    .OrderByDescending(ah => ah.Date)
                    .FirstOrDefault();

                // sale exists
                if (sale != null)
                {
                    result = sale.Status == Statuses.Active;
                }
            }

            return result;
        }
    }

another example using your DbContext:

    [NotMapped]
    public bool CalculateActiveListing
    {
        get
        {
            bool result = false;

            // your logic to calculate then set to "result" variable.
            // for example:

            using (var context = new MyDbContext())
            {
                var sale = context.SalesRecords
                    .Where(ah => ah.Date <= GlobalCoordinatedDateTime.Local)
                    .Where(ah => ah.ProductModelNumber == ModelNumber && ah.ProductSerialNumber == SerialNumber)
                    .OrderByDescending(ah => ah.Date)
                    .FirstOrDefault();

                // sale exists
                if (sale != null)
                {
                    result = sale.Status == Statuses.Active;
                }
            }

            return result;
        }
    }

sorry, my bad english.

Can you explain how I get the data I need to calculate `result` from the DB table in this proposal? — Kyle V., Mar 20 '19 at 16:44
I don't know how you build your access data logic, but you can something like calling a method from a class that use the context... and then your result variable will be: ``` bool result = SomeClass.CalculateActiveListing(); ``` — Oscar David Diaz Fortaleché, Mar 20 '19 at 16:50
Okay I see. So if I use the context in my logic to fetch some other Table for example, will that be translated into a SQL query or will that result in two SQL queries and the logic being executed in C#? — Kyle V., Mar 20 '19 at 16:53
There are many ways... but If your ProductEntity entity has a relationship with SalesRecords entity, this could be more easy. How i see, you have the query ready, only you need to separe this especial query in a method and call it. — Oscar David Diaz Fortaleché, Mar 20 '19 at 16:59
A small example of how to get the context and use it like that would be really helpful — Kyle V., Mar 20 '19 at 17:16
Some examples here about querying in Entity Framework: http://www.entityframeworktutorial.net/Querying-with-EDM.aspx — Oscar David Diaz Fortaleché, Mar 20 '19 at 18:29
Actually I meant using your example with `public bool ActiveListing` in the `get` method, how would I access the context and/or the `SalesRecords` objects I need? — Kyle V., Mar 20 '19 at 18:34

Linq to SQL column computed from query to different table?

1 Answers1