15

Most of my server side experience is with nodeJS meteor or rails so i dont have a wealth of .Net knowledge yet. In rails its pretty easy to pick on ORM because ActiveRecord is the only game in town or so it seems. My approach was typically query everything you can using the ORM and if you cant pull it off then drop down to raw SQL.

However I am trying to read tutorials and lessons on querying my databases in .Net 4.5 or newer apps and I am confused on what the way of the future is. I see tutorials in the following

  • Linq Lambda syntax
  • Linq Query syntax
  • Entity Framework

What is the suggested ORM for modern .net apps? It seems like i can do basic CRUD operations in all of the above. But will one be better suited for say joins across tables? Mind you I only ever plan to query against Microsoft SQL server databases for my .net solutions.

what really confused me is reading that EF replaced linq but then reading that EF also uses linq. So when i see an example to say select all columns i dont know if i am using a syntax of the past or future. Also just looking at code i cant tell if its linq or EF. I can tell if its query or lambda syntax though.

ngnewb
  • 253
  • 1
  • 4
  • 14
  • LINQ is a .NET concept that is supported by 'providers'. EF has a LINQ provider, as does NHibernate. EF and NH will convert, behind the scenes, the expression trees created from the LINQ into SQL queries. – David Osborne May 12 '16 at 14:37
  • No, ActiveRecord is not the only game in town unless you only work low traffic applications that only deal with a few rows at a time. Why work with only *one* record at a time when you can load all the objects you want and bind to them? Which is why Micro-ORMs are preferable for a *lot* of scenarios. ORMs are *not* for CRUD operations either. If you don't have entities with custom behaviours, you don't need the `O` part – Panagiotis Kanavos May 12 '16 at 14:38
  • http://stackoverflow.com/questions/5101974/net-orm-comparison – David Osborne May 12 '16 at 14:42
  • You should use F# and this: http://fsprojects.github.io/SQLProvider/ – Tuomas Hietanen Sep 07 '16 at 10:00

3 Answers3

10

The options you mentioned are not all ORMs, in this 3 the only ORM is EF. there are other popular ORMs in .net including:

  • NHibernate
  • LLBLGen pro
  • ActiveRecord

Most of them support LINQ. EntityFramework is the younger one but it's integration with VS and being available out of the box is making it the most popular ORM in .net and it replaced LINQ to Entities not LINQ itself. LINQ (Language-Integrated Query) as defined in msdn can be used with different datasources.

LINQ is a set of features that extends powerful query capabilities to the language syntax of C#. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. The .NET Framework includes LINQ provider assemblies that enable the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET Datasets, and XML documents.

There are two different syntaxes to use LINQ mostly known as Fluent syntax and Query syntax.

If your new to .net as you mentioned, I suggest using EF with fluent syntax it would be the quickest way to start querying database in .net.

Bob Horn
  • 33,387
  • 34
  • 113
  • 219
VahidNaderi
  • 2,448
  • 1
  • 23
  • 35
  • would i have to switch from EF with fluent syntax to query syntax in certain scenarios (joins across tables) or do they have a similar subset of functionality? – ngnewb May 12 '16 at 15:12
  • 1
    You can find a good discussion about it [here](http://stackoverflow.com/questions/214500/linq-fluent-and-query-expression-is-there-any-benefits-of-one-over-other) – VahidNaderi May 12 '16 at 15:19
  • can i mix syntaxs in different models or the same model or is that a bad idea? – ngnewb May 12 '16 at 15:24
  • @ngnewb Yes, you can mix and match as you desire. The fluent/lambda/method syntax will never go away as ultimately that is how the query syntax works in the background. Query syntax is just syntactic sugar on top of the fluent/lamda/method syntax, and ultimately gets converted before compilation. – Robert McKee May 12 '16 at 17:55
6

The short answer is Entity Framework.

I think what's confusing you is that you can use Linq Lambda and Linq Query syntax with Entity Framework. But the future (in my opinion) is Fluent API.

I've been using Entity Framework 7 (pre-release) and I get the impression that it's focused on Fluent API. Which is fine with me because I like it much better than Linq syntax.

Clint B
  • 4,610
  • 2
  • 18
  • 22
  • but right now entity framework 6 is the standard? so like if i want to join across 3 tables , this would be a good answer http://stackoverflow.com/questions/21051612/entity-framework-join-3-tables – ngnewb May 12 '16 at 14:47
  • also is there any way to know that i am looking at entity framework code when looking at a model or a query? I try to only look at SO posts 3 years old or newer to assume its at least .net 4.5 or newer – ngnewb May 12 '16 at 14:48
  • @ngnewb - I don't think it really matters because the syntax is the same. But I would pick one type of syntax and stick with it. – Clint B May 12 '16 at 14:53
  • 1
    Just as the OP you're comparing apples and oranges. "Fluent API" is *mostly* used to indicate the mapping API. When talking about LINQ, there is fluent syntax (or method syntax) – which means calling the `Queryable` extension methods, or there is query syntax (or comprehensive syntax) – which means a SQL-like syntax to build LINQ queries in code. *Both are LINQ syntax*, and it really doesn't make sense to dogmatically prefer one above the other. Use the one that produces the best readable code for the task in hand. – Gert Arnold May 12 '16 at 14:56
  • @ngnewb - Also, the same thing goes for EF when it can't handle what you want to do. Drop back to using SQL. You will want to use ADO.NET when doing that. Using EF to execute a stored procedure has a lot of overhead and EF tries to figure out the SP. Which if you're doing something that EF can't do, EF is almost never successful at figuring out your SP and it gets messy. – Clint B May 12 '16 at 15:02
  • @GertArnold - Well since you can query the DB in Lambda syntax, query syntax or fluent syntax and have the same results, I think it does make sense to pick the one you prefer and stick with it. I used the term Fluent API as a catch all like many people do. – Clint B May 12 '16 at 15:09
  • By using catch-all phrases, in the end nobody knows what you mean exactly. In your answer you seem to use "Linq Lambda and Linq Query syntax" as opposites of "Fluent API". It's not that I like nitpicking, but reading that sentence I *really* don't know what you mean. – Gert Arnold May 12 '16 at 15:39
  • @GerArnold - Ok, point taken. Your comments have made me rethink my use of the term. – Clint B May 12 '16 at 16:39
0

Try Vega https://github.com/aadreja/vega it is simple to use and one of the fastest .net ORM with a lot of exciting features.

Following are steps to begin:-

1) Create an Entity class derived from Vega.EntityBase Example: -

[Table(NeedsHistory = true)]
public class Country : EntityBase
{
    [PrimaryKey(true)]
    public int Id { get; set; }
    public string Name { get; set; }
    public string ShortCode { get; set; }
}

2) Perform CRUD operations

Insert - Simply create Entity & set properties for Insert. Create a connection object, pass it to Repository & call Add method which will return Id of newly create record

Country country = new Country()
    {
        Name = "India",
        ShortCode = "IN",
        CreatedBy = 1  //user id created record
    };

using (SqlConnection connection = new SqlConnection(conString))
{
     Repository<Country> countryRepo = new Repository<Country>(connection);
     int result = (int)countryRepo.Add(country);
}

Update - To update record use Update method of repository

City city = new City()
{
    Id = 1,
    Name = "Ahmedabad",
    State = "GU",
    CountryId = 1,
    Longitude = 102.23m,
    Latitude = 124.23m,
    UpdatedBy = 1
};

using (SqlConnection con = new SqlConnection(connectionString))
{
     Repository<City> cityRepo = new Repository<City>(con);
     bool result = cityRepo.Update(city);
}

Read List - Reads all records with specified criteria & converts it to enumerable object which can be converted to List.

//read all records
using (SqlConnection con = new SqlConnection(connectionString))
{
     Repository<City> cityRepo = new Repository<City>(con);
     List<City> cityList  = cityRepo.ReadAll().ToList();
}

//read all with specified dynamic criteria
using (SqlConnection con = new SqlConnection(connectionString))
{
     Repository<City> cityRepo = new Repository<City>(con);
     List<City> cityList = cityRepo.ReadAll("Name=@Name", new { Name = "Ahmedabad" }).ToList(); //Dynamic criteria
}

Refer more documentation at https://github.com/aadreja/vega/wiki/Repository

NightOwl888
  • 55,572
  • 24
  • 139
  • 212
aadreja
  • 1
  • 2