C#•2y ago

❔ How to ORM joins using sqlite-net?

I am currently using .NET6 and this sqlite https://github.com/praeclarum/sqlite-net for a small project. I wanted to know what the best way to get a good ORM going. I used to use Dapper in the past for System.Data.Sqlite but that has issues of its own. Say we have an example schema (primary key, auto increment , db layout are irrelevant so dw about it, just using this for an example and not in the real world)

public class House{
 public int Id {get;set;}
 public string Name {get;set;}
}

public class HouseCost{
 public int Id {get;set;}
 public int HouseId {get;set;}
 public decimal Cost {get;set;}
}

public class HouseDto{
  public House house {get;set;} 
  public HouseCost {get;set;}
}

public class House{
 public int Id {get;set;}
 public string Name {get;set;}
}

public class HouseCost{
 public int Id {get;set;}
 public int HouseId {get;set;}
 public decimal Cost {get;set;}
}

public class HouseDto{
  public House house {get;set;} 
  public HouseCost {get;set;}
}

How can I bind this inner join into HouseDto? SELECT * FROM House INNER JOIN HouseCost ON HouseCost.HouseId = House.Id Now, I know I can just make a class and paste them in such as

public class HouseDto{
      public int Id {get;set;}
      public int HouseId {get;set;}
      public decimal Cost {get;set;}
     public string Name {get;set;}
}

public class HouseDto{
      public int Id {get;set;}
      public int HouseId {get;set;}
      public decimal Cost {get;set;}
     public string Name {get;set;}
}

But there are 2 issues with this approach. Issue #1. It gets messy, like real messy if I want to add/change some things. Issue #2. How would it know where to Bind HouseId and HouseCost's Id, since they are both the same name. I'd assume I'd need to have an attribute to make them unique? With dapper, this was solved by letting it bind to each individual object, which would prevent all these issues. But I cannot seem to figure out how it's done in sqlite-net. I know it may not be possible with this library. If it isn't possible, is there a library similar to this that supports Async reading/writing with full multi-threading with the basic .Insert(obj) and Query<T>() sql-net offers?

12 Replies

TheRanger•2y ago

sqlite-net? the most used ORMs are Entity framework, then Dapper you could try giving a shot with Entity Framework i personally use it with sqlite

PuckOP•2y ago

Oh no I don't touch EF, it's too messy unfortunately Pretty much like System.Data.Sqlite but with a few methods to make life easier Sadly dapper doesn't support it and only System.data.sqlite, but that brings complications in itself. If there are no other then I may be forced to switch over

TheRanger•2y ago

EF is messy? 😐

Pobiega•2y ago

I've heard very good things about RepoDB and it supports sqlite (https://repodb.net/tutorial/get-started-sqlite), might be worth checking out if you dont like the alternatives

jcotton42•2y ago

EF? Messy? How?

FusedQyou•2y ago

Lol EF is not messy

PuckOP•2y ago

I won't go into the details EF is just a hot topic to argue against. if I have bad experiences with it doesn't mean others will though. I'm fairly picky :/ I'll check this out

FusedQyou•2y ago

I would love to hear your argument against EF

PuckOP•2y ago

This is beautiful, it has what I need. Thanks Oh man, in the past I was arguing over it in another server and there were always more people who bashed me for my reasonings than anything. A few agreed, but majority got triggered. EF is complex and I'm not that experienced with it, that's all I'm gonna say. To me it gets messy real fast and I much prefer manually doing things. But I guess on a larger scale, when dealing with a very, very, very huge schema, EF may not be the messy way to get things done.

FusedQyou•2y ago

So it's just a lack of experience that makes it messy? 😛 That's fixable, atleast

PuckOP•2y ago

Yea most likely lol

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ How to ORM joins using sqlite-net?