Linq to NHibernate

My previous codes show the use of NHibernate and Fluent NHibernate as OR/M with .NET framework. Next, I would refer to Fluent NHibernate and use Linq.

1. Download NHibernate.Linq-2.1.2-GA-Bin to get NHibernate.Linq.dll.
2. Add reference to NHibernate.Linq.dll in this file that we work with, and in the beginning add using NHibernate.Linq;
3. At line 43, add this:

  var x = (from p in session.Linq<Personal>() where p.Member== "Y" select p).Take(5);
  Console.WriteLine("Output #1:");
  foreach (var a in x)
    Console.WriteLine("id= {0}, ket= {1}", a.Id, a.Sex);

  var r = from p in session.Linq<Personal>()
          group p by p.Sex into g
          select new { sex = g.Key, count = g.Count() };
  Console.WriteLine("Output #2:");
  foreach (var a in r)
    Console.WriteLine("Sex = {0}, Count= {1}", a.sex, a.count);

4. In this sample, I add a new table and make mapping to database.

public class Personal
    {
        public virtual string Id { get; private set; }
        public virtual string Member { get; set; }
        public virtual string Sex { get; set; }
    }

public class PersonalMap : ClassMap<Personal>
{
  public PersonalMap()
  {
    Table("employees");
    Id(x => x.Id).Column("pk");
    Map(x => x.Member, "member");
    Map(x => x.Sex, "sex");
  }
}

Below the output, and it's time for me to sleep....good morning..

LINQ to MySQL with DbLinq

Language-Integrated Query (LINQ), allows .NET programs to connect to databases. It is an Object-Relational mapping tool, with some similarities to Hibernate or LLBLGen. LINQ is type-safe, queries get compiled into MSIL on the fly, and your C# WHERE clauses are translated into SQL and sent to SQL server for execution. In short, it makes design of data access layers safer and faster.

Today I have work with LINQ to MySQL, and arrived at DbLinq.
DbLinq is the LINQ provider that allows to use common databases with an API close to Linq to SQL. DbLinq is a reimplementation of System.Data.Linq.dll for use with SQL servers. It supports MySQL, Oracle, PostgreSQL, SQLite, Ingres, Firebird, and SQL Server.

This code use DbLinq 0.20.1 (released 2010-04-16).
1. Download and extract DbLinq to c:\DbLinq-0.20.1
2. Copy run_myMetal.bat to runme.bat, and modify it to
DbMetal.exe /conn:"server=localhost;user id=myname;password=a; port=3306; database=mydb" /provider=MySql /code:FerDBLinq.cs /language:C#
4. Execute runme.bat so we get FerDBLinq.cs. Copy the file into your project folder then use Add Existing items from Solution Explorer.

5. Add reference to DbLinq.dll, DbLinq.MySql.dll
6. Open FerDBLing.cs file, add these in the beginning:

using DbLinq.MySql;
using DbLinq.Data.Linq;
using System.Data.Linq.Mapping;
using MySql.Data.MySqlClient;

Here code snippets that follows:

public partial class myDBS: MySqlDataContext
{
    #region Extensibility Method Declarations
  partial void OnCreated();
    #endregion
    public myDBS(string conStr) : base(new MySqlConnection(conStr)) { }
    public myDBS(MySqlConnection conn): base(conn){}

    public DbLinq.Data.Linq.Table<BUkUBeSaR> BUkUBeSaR
    {
      get { return this.GetTable<BUkUBeSaR>();}
    }

5. Creata a function for testing purpose.

private void linqing()
{
  using (MySqlConnection conn = new MySqlConnection(GconString))
  {
    try
    {
      conn.Open();
      myDBS db = new myDBS(conn);

      var q = (from p in db.PerSoNaL where p.member=="Y";
      Console.WriteLine("Output #1:");
      foreach (var a in q)
        Console.WriteLine("id= {0}, ket= {1}", a.PK, a.sex);
 
      var r = from p in db.PerSoNaL group p by p.sex into g
              select new { sex = g.Key, Count = g.Count() };
      Console.WriteLine("Output #2:");
      ObjectDumper.Write(r);

      var t = db.PerSoNaL.GroupBy(p => p.sex);
      var u = t.Select(g => new {g.Key, Count = g.Count()});
      Console.WriteLine("Output #3:");
      foreach (var a in u)
          Console.WriteLine("sex={0}, count={1}", a.Key, a.Count);

      int cnt = (from p in db.PerSoNaL group p by p.sex).Count();
      Console.WriteLine("Output #4:\nCount={0}", cnt);

      var s=db.PerSoNaL.GroupBy(c=> c.sex).ToList().AsQueryable();
      Console.WriteLine("Output #5:");
      foreach (var a in s)
         Console.WriteLine("sex "+a.Key+ ", count=" +a.Count());
      }
      catch (Exception ex)
      {
         MessageBox.Show(ex.Message);
       }
    }
}

The output:
Dblinq works for me, but I could not resolve about the count(), for Male(L) and Female(P) always return 1. I've read issue 157 and 158, but still locked. Do you have any idea?