locked
Relationships between SQL Server and StackExchange.Redis (NoSQL) RRS feed

  • Question

  • User-375835195 posted

    Recently, I decided to use Redis for memory cache because it's faster and it's not a good idea to create additional tables just because of one specific strategy of mine.

    I have two classes that have a relationship between each other and BotSession, respectively, has a relationship with another class from MSSQL.

    These relationships are vital. Is this possible? If not what would you recommend me? I decided to use the cache because it fetches faster.

    builder.Entity<BotSession>(entity =>
    {
        entity.HasOne(bs => bs.Bot)
            .WithMany(b => b.BotSessions)
            .HasForeignKey(bs => bs.BotId)
            .OnDelete(DeleteBehavior.Cascade);
    
        // Unique key
        entity.HasIndex(bs => new { bs.TrendType, bs.BotId })
            .IsUnique();
    });
    
    builder.Entity<Aggregation>(entity =>
    {
        entity.HasOne(a => a.BotSession)
            .WithMany(bs => bs.Aggregations)
            .HasForeignKey(a => a.BotSessionId)
            .OnDelete(DeleteBehavior.Cascade);
    
        // Unique key
        entity.HasIndex(a => new { a.Type, a.BotSessionId })
            .IsUnique();
    });

    public class Aggregation
    {
        public Aggregation()
        {
        }
    
        public Aggregation(AggregationType type, BinanceKline kline, int botSessionId)
        {
            Type = type;
            OpenTime = kline.OpenTime;
            CloseTime = kline.CloseTime;
            Open = kline.Open;
            High = kline.High;
            Low = kline.Low;
            Close = kline.Close;
            Volume = kline.Volume;
            BotSessionId = botSessionId;
        }
    
        public int Id { get; set; }
        public DateTime OpenTime { get; set; }
        public DateTime CloseTime { get; set; }
    
        [Column(TypeName = "decimal(18,8)")]
        public decimal Open { get; set; }
    
        [Column(TypeName = "decimal(18,8)")]
        public decimal High { get; set; }
    
        [Column(TypeName = "decimal(18,8)")]
        public decimal Low { get; set; }
    
        [Column(TypeName = "decimal(18,8)")]
        public decimal Close { get; set; }
    
        [Column(TypeName = "decimal(18,8)")]
        public decimal Volume { get; set; }
    
        public AggregationType Type { get; set; }
    
        public int BotSessionId { get; set; }
        public BotSession BotSession { get; set; }
    }
    
    public class BotSession
    {
        public BotSession()
        {
        }
    
        public BotSession(TrendType trendType, int botId)
        {
            TrendType = trendType;
            CanTrade = false;
            StopLossValue = null;
            BotId = botId;
        }
    
        public int Id { get; set; }
        public TrendType TrendType { get; set; }
        public bool CanTrade { get; set; }
    
        [Column(TypeName = "decimal(18,8)")]
        public decimal? StopLossValue { get; set; }
    
        [Column(TypeName = "decimal(18,8)")]
        public decimal? NTimes { get; set; }
    
        public int BotId { get; set; }
        public Bot Bot { get; set; }
    
        public List<Aggregation> Aggregations { get; set; }
    
        public bool IsUptrendSession()
            => TrendType == TrendType.Uptrend;
    }

    public static class CacheExtensions
    {
        public static async Task<T> SetAsync<T>(this IDistributedCache cache, string key, T item)
        {
            var json = JsonConvert.SerializeObject(item);
    
            await cache.SetStringAsync(key, json);
    
            return await cache.GetAsync<T>(key);
        }
    
        public static async Task<T> SetAsync<T>(this IDistributedCache cache, string key, T item, int expirationInHours)
        {
            var json = JsonConvert.SerializeObject(item);
    
            await cache.SetStringAsync(key, json, new DistributedCacheEntryOptions
            {
                AbsoluteExpirationRelativeToNow = TimeSpan.FromHours(expirationInHours)
            });
    
            return await cache.GetAsync<T>(key);
        }
    
        public static async Task<T> GetAsync<T>(this IDistributedCache cache, string key)
        {
            var json = await cache.GetStringAsync(key);
    
            if (json == null)
                return default;
    
            return JsonConvert.DeserializeObject<T>(json);
        }
    }
    
    // usage
    var asd = await _cache.GetAsync<List<BotSession>>("Hey");
    await _cache.SetAsync("Hey", sessions);
    await _cache.RemoveAsync("Hey");

    Sunday, November 10, 2019 7:57 PM

Answers

  • User-719153870 posted

    Hi Hulkstance,

    These relationships are vital. Is this possible?

    Don't think so, Redis is NoSQL which means non relational.

    I suggest you can select all records from your database into one table then store in memory with Redis which should be a better practice to make use of Redis.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 11, 2019 7:19 AM

All replies

  • User-719153870 posted

    Hi Hulkstance,

    These relationships are vital. Is this possible?

    Don't think so, Redis is NoSQL which means non relational.

    I suggest you can select all records from your database into one table then store in memory with Redis which should be a better practice to make use of Redis.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 11, 2019 7:19 AM
  • User-375835195 posted

    I found out the same. There can't be relationships between relational DB and NoSQL db.

    Thursday, November 14, 2019 9:42 PM