It's possible to add a custom column with linq2db?

Question

I am using linq2db with the FluentMappingBuilder to map my database, and I want to know if I can map an custom column to my class.

This is my query.

SELECT p.Id, 
       p.Name, 
       p.Price, 
       dbo.FN_DISTANCE_HAVERSINE(a.latitude, a.longitude, 48.135538400663656, 11.551979373610068) AS Distance
FROM Product p 
INNER JOIN Category c ON p.IdCategory = c.Id
INNER JOIN Store s ON p.IdStore = s.Id
INNER JOIN Address a ON s.IdAddress = a.Id
WHERE Active = 1
AND CanLoad = 1
ORDER BY distance ASC

I added Distance to my product class, and set as not a column Property(x => x.DistanceKm).IsNotColumn(); in my mapping class

But I don't know how to make this query using linq2db. It's possible?

I also tried another approach, instead of using a Sql function, do everything with linq2db. And works fine...

IQueryable<Product> products = GetQueryable();

products = from p in products
                           select new Product
                           {
                               Id = p.Id,
                               Name = p.Name,
                               Price = p.Price,
                               DistanceKm = DistanceHelper.GetDistanceWithHaversine(productFilter.Latitude, productFilter.Longitude, p.Store.Address.Latitude, p.Store.Address.Longitude)
                           };

var result = await products.ToListAsync();

but if I try to order Products by DistanceKm, I get an Exception:

var result = await products.OrderByDescending(p => p.DistanceKm).ToListAsync();

Exception

GetDistanceWithHaversine(value(ProductRepository+<>c__DisplayClass3_0).productFilter.Latitude, value(StoreAggregator.Api.Data.Repository.ProductRepository+<>c__DisplayClass3_0).productFilter.Longitude, p.Store.Address.Latitude, p.Store.Address.Longitude)' cannot be converted to SQL.
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToSql(IBuildContext context, Expression expression, Boolean unwrap, ColumnDescriptor columnDescriptor, Boolean isPureExpression)

Is it a limitation of linq2db, or can I do with one of these approach?

Edit
My GetDistanceWithHaversine implementation.
The dbo.FN_DISTANCE_HAVERSINE function does exactly the same, but on the sql server.

public static double GetDistanceWithHaversine(double lat1, double lng1, double lat2, double lng2)
{
    var R = 6371; // Radius of the earth in km
    var dLat = Deg2rad(lat2 - lat1);  // deg2rad below
    var dLon = Deg2rad(lng2 - lng1);
    var a =
        Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
        Math.Cos(Deg2rad(lat1)) * Math.Cos(Deg2rad(lat2)) *
        Math.Sin(dLon / 2) * Math.Sin(dLon / 2);

    var c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
    var d = R * c; // Distance in km
    return d;
}

private static double Deg2rad(double deg)
{
    return deg * ((Math.PI) / 180);
}

Show in question `GetDistanceWithHaversine` implementation. – Svyatoslav Danyliv Feb 10 '21 at 09:27 — Svyatoslav Danyliv, Feb 10 '21 at 09:27

Svyatoslav Danyliv · Accepted Answer · 2021-02-10T14:43:22.707

Solution is simple. You have to tell linq2db that this function has database analogue. For your case it is Sql.FunctionAttribute

[Sql.Function("dbo.FN_DISTANCE_HAVERSINE")]
public static double GetDistanceWithHaversine(double lat1, double lng1, double lat2, double lng2)
{
    var R = 6371; // Radius of the earth in km
    var dLat = Deg2rad(lat2 - lat1);  // deg2rad below
    var dLon = Deg2rad(lng2 - lng1);
    var a =
        Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
        Math.Cos(Deg2rad(lat1)) * Math.Cos(Deg2rad(lat2)) *
        Math.Sin(dLon / 2) * Math.Sin(dLon / 2);

    var c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
    var d = R * c; // Distance in km
    return d;
}

It's possible to add a custom column with linq2db?

1 Answers1