Microsoft Technologies Complex Query এবং Subquery Techniques গাইড ও নোট

NHibernate দিয়ে আপনি Complex Queries এবং Subqueries করতে পারেন, যা ডেটাবেস থেকে অনেক জটিল তথ্য বের করতে সহায়ক। NHibernate এর Criteria API, HQL (Hibernate Query Language) এবং Native SQL Queries ব্যবহার করে এমন কুয়েরি তৈরি করা যায় যা JOIN, GROUP BY, HAVING, Subquery ইত্যাদি একত্রিত করে ডেটা রিট্রাইভ করে।

এখানে NHibernate এ Complex Query এবং Subquery Techniques ব্যবহার করার কিছু উদাহরণ দেখানো হলো।

1. Complex Query with JOIN

এখানে আমরা একটি JOIN কুয়েরি তৈরি করবো, যেখানে দুটি টেবিলের মধ্যে সম্পর্ক রয়েছে। ধরুন, আমাদের দুটি টেবিল Employee এবং Department আছে, এবং আমরা চাই দুটি টেবিলের কিছু তথ্য একত্রিত করে কুয়েরি রিটার্ন করতে।

1.1 Entity Classes:

public class Employee
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Department Department { get; set; }
}

public class Department
{
    public virtual int Id { get; set; }
    public virtual string DepartmentName { get; set; }
}

1.2 Complex Query with JOIN using HQL:

using (var session = sessionFactory.OpenSession())
{
    var query = session.CreateQuery("SELECT e.Name, d.DepartmentName FROM Employee e " +
                                    "JOIN e.Department d WHERE d.DepartmentName = :deptName")
                       .SetParameter("deptName", "IT");

    var result = query.List<object[]>();

    foreach (var row in result)
    {
        var employeeName = (string)row[0];
        var departmentName = (string)row[1];
        Console.WriteLine($"Employee: {employeeName}, Department: {departmentName}");
    }
}

এখানে:

• HQL (Hibernate Query Language) ব্যবহার করা হয়েছে যেখানে আমরা Employee এবং Department টেবিলের মধ্যে JOIN করেছি।
• JOIN এর মাধ্যমে একাধিক টেবিল থেকে ডেটা একত্রিত করা হয়েছে এবং WHERE ক্লজের মাধ্যমে একটি নির্দিষ্ট DepartmentName ফিল্টার করা হয়েছে।

2. Subquery Techniques

Subqueries হল এক ধরনের কুয়েরি যা অন্য একটি কুয়েরির ভিতরে থাকে। NHibernate এর মাধ্যমে আমরা সাবকুয়েরি ব্যবহার করে আরও জটিল কুয়েরি তৈরি করতে পারি।

2.1 Subquery Example using Criteria API:

ধরা যাক, আমরা এমন একটি কুয়েরি তৈরি করতে চাই যা Employee টেবিল থেকে এমন সব Employees রিটার্ন করবে যাদের Salary মোট Department-এর গড় Salary থেকে বেশি।

using (var session = sessionFactory.OpenSession())
{
    var subquery = DetachedCriteria.For<Employee>()
                                   .SetProjection(Projections.Avg("Salary"))
                                   .Add(Restrictions.Eq("Department.Id", 1));  // Filter by department

    var query = session.CreateCriteria<Employee>()
                       .Add(Restrictions.Gt("Salary", subquery))  // Compare employee salary with avg salary
                       .List<Employee>();

    foreach (var employee in query)
    {
        Console.WriteLine($"Employee: {employee.Name}, Salary: {employee.Salary}");
    }
}

এখানে:

• DetachedCriteria ব্যবহার করে একটি সাবকুয়েরি তৈরি করা হয়েছে, যেখানে গড় Salary বের করা হয়েছে নির্দিষ্ট Department এর জন্য।
• মূল কুয়েরি থেকে Employee সিলেক্ট করা হয়েছে যাদের Salary গড় Salary থেকে বেশি।

2.2 Subquery Example using HQL:

ধরা যাক, এবার আমরা HQL ব্যবহার করে সাবকুয়েরি তৈরি করবো। আমরা Employee টেবিল থেকে সেইসব Employees খুঁজে বের করতে চাই যাদের Salary বেশি, যেগুলির গড় Salary সাবকুয়েরি থেকে বের করা হবে।

using (var session = sessionFactory.OpenSession())
{
    var query = session.CreateQuery("FROM Employee e WHERE e.Salary > (SELECT avg(e2.Salary) FROM Employee e2 WHERE e2.Department = e.Department)");

    var employees = query.List<Employee>();

    foreach (var employee in employees)
    {
        Console.WriteLine($"Employee: {employee.Name}, Salary: {employee.Salary}");
    }
}

এখানে:

• আমরা HQL সাবকুয়েরি ব্যবহার করেছি, যেখানে একটি কুয়েরি অন্য কুয়েরির ফলাফল দ্বারা ফিল্টার করা হচ্ছে। Employee টেবিলের Salary তুলনা করা হয়েছে সাবকুয়েরি থেকে বের হওয়া গড় Salary এর সাথে।

3. Complex Query with GROUP BY and HAVING

GROUP BY এবং HAVING ব্যবহৃত হয় যখন আপনি কিছু গ্রুপিং এবং ফিল্টারিং করতে চান। উদাহরণস্বরূপ, আমরা চাই এমন সমস্ত Departments দেখাতে যাদের মোট Salary ১০০০০০ এর বেশি।

3.1 GROUP BY and HAVING Example using HQL:

using (var session = sessionFactory.OpenSession())
{
    var query = session.CreateQuery("SELECT d.DepartmentName, SUM(e.Salary) " +
                                    "FROM Employee e " +
                                    "JOIN e.Department d " +
                                    "GROUP BY d.DepartmentName " +
                                    "HAVING SUM(e.Salary) > 100000");

    var result = query.List<object[]>();

    foreach (var row in result)
    {
        var departmentName = (string)row[0];
        var totalSalary = (decimal)row[1];
        Console.WriteLine($"Department: {departmentName}, Total Salary: {totalSalary}");
    }
}

এখানে:

• GROUP BY ব্যবহার করা হয়েছে DepartmentName অনুযায়ী গ্রুপ করতে এবং SUM(e.Salary) দিয়ে মোট Salary হিসাব করা হয়েছে।
• HAVING ক্লজ দিয়ে গ্রুপগুলির মধ্যে ফিল্টার করা হয়েছে, যাতে শুধুমাত্র সেই Department গুলি আসে যাদের মোট Salary ১০০০০০ এর বেশি।

4. Complex Query with Multiple Subqueries

আপনি Multiple Subqueries ব্যবহার করেও আরও জটিল কুয়েরি তৈরি করতে পারেন। এখানে আমরা দুটি সাবকুয়েরি ব্যবহার করবো: একটি Department অনুযায়ী গড় Salary বের করবে এবং আরেকটি সাবকুয়েরি, যা নির্দিষ্ট Salary এর অধীনে কর্মচারীদের দেখাবে।

4.1 Multiple Subqueries Example:

using (var session = sessionFactory.OpenSession())
{
    var subquery1 = DetachedCriteria.For<Employee>()
                                    .SetProjection(Projections.Avg("Salary"))
                                    .Add(Restrictions.Eq("Department.Id", 1));

    var subquery2 = DetachedCriteria.For<Employee>()
                                    .SetProjection(Projections.Property("Salary"))
                                    .Add(Restrictions.Lt("Salary", 50000));

    var query = session.CreateCriteria<Employee>()
                       .Add(Restrictions.Gt("Salary", subquery1))  // First subquery: Salary > Department average
                       .Add(Restrictions.In("Salary", subquery2))  // Second subquery: Salary < 50000
                       .List<Employee>();

    foreach (var employee in query)
    {
        Console.WriteLine($"Employee: {employee.Name}, Salary: {employee.Salary}");
    }
}

এখানে:

• দুটি DetachedCriteria সাবকুয়েরি ব্যবহৃত হয়েছে। প্রথমটি গড় Salary বের করেছে এবং দ্বিতীয়টি Salary ফিল্টার করেছে।
• Restrictions.Gt এবং Restrictions.In ব্যবহার করা হয়েছে সাবকুয়েরি রেজাল্টসের সাথে তুলনা করার জন্য।

সারাংশ

NHibernate ব্যবহার করে Complex Queries এবং Subqueries তৈরি করা খুবই শক্তিশালী এবং নমনীয় পদ্ধতি। এর মাধ্যমে আপনি JOIN, GROUP BY, HAVING, এবং Subquery সমন্বয় করে যেকোনো জটিল কুয়েরি তৈরি করতে পারবেন। NHibernate এর Criteria API, HQL, এবং Native SQL এগুলির সবই আপনাকে দ্রুত এবং কার্যকরী ডেটা রিট্রাইভ করার সুযোগ দেয়।

Complex Queries এবং Nested Subqueries হল SQL কুয়েরি লেখার জন্য উন্নত কৌশল, যা বিভিন্ন টেবিলের মধ্যে জটিল সম্পর্ক ও ডেটা প্রক্রিয়াকরণ করতে সাহায্য করে। NHibernate-এ এই ধরনের কুয়েরি তৈরি করতে আপনাকে HQL (Hibernate Query Language) বা Native SQL ব্যবহার করতে হবে, যেগুলি ডেটাবেসের জটিল কুয়েরি পরিচালনা করতে সক্ষম।

Complex Query কী?

একটি Complex Query সাধারণত একাধিক শর্ত, জয়েন (join), গ্রুপিং (grouping), অর্ডারিং (ordering) ইত্যাদি শর্ত নিয়ে তৈরি হয়। NHibernate-এ যখন আপনি জটিল কুয়েরি তৈরি করবেন, তখন আপনাকে HQL বা Criteria API ব্যবহার করে কাঙ্ক্ষিত ডেটা প্রক্রিয়া করতে হবে।

Complex Query তৈরি করা (HQL)

ধরা যাক, Employee এবং Department টেবিলের মধ্যে একটি জটিল কুয়েরি তৈরি করতে হবে যেখানে একজন কর্মী একটি নির্দিষ্ট বয়সের (30 বছর) বেশি এবং তাদের Department এর নাম 'IT' হতে হবে।

using (ISession session = sessionFactory.OpenSession())
{
    var query = session.CreateQuery(
        "SELECT e FROM Employee e " +
        "WHERE e.age > :age AND e.department.name = :deptName")
        .SetParameter("age", 30)
        .SetParameter("deptName", "IT");

    var employees = query.List<Employee>();

    foreach (var employee in employees)
    {
        Console.WriteLine($"Id: {employee.Id}, Name: {employee.Name}, Age: {employee.Age}");
    }
}

এখানে, e.age > :age এবং e.department.name = :deptName এর মাধ্যমে দুটি শর্ত যুক্ত করা হয়েছে। প্রথম শর্তে বয়স এবং দ্বিতীয় শর্তে Department নামের উপর ফিল্টার করা হচ্ছে।

Nested Subquery কী?

Nested Subquery হল এক ধরনের কুয়েরি যেখানে একটি সাব-কুয়েরি আরেকটি কুয়েরির মধ্যে থাকে। এটি সাধারণত একাধিক টেবিলের মধ্যে ডেটা সম্পর্কিত প্রশ্নের উত্তর খুঁজতে ব্যবহৃত হয়। এক বা একাধিক সাব-কুয়েরি মূল কুয়েরির ভেতর থাকে, এবং এই সাব-কুয়েরি মূল কুয়েরির ফলাফল নির্ধারণে সাহায্য করে।

Nested Subquery Example (HQL)

ধরা যাক, আপনাকে এমন একটি কুয়েরি তৈরি করতে হবে যেখানে Employee টেবিল থেকে এমন কর্মীদের নির্বাচন করতে হবে যারা তাদের Department এর সবচেয়ে উচ্চ বেতন প্রাপ্ত কর্মী নন।

using (ISession session = sessionFactory.OpenSession())
{
    var query = session.CreateQuery(
        "FROM Employee e " +
        "WHERE e.salary < (SELECT MAX(e2.salary) FROM Employee e2 WHERE e2.department.id = e.department.id)"
    );

    var employees = query.List<Employee>();

    foreach (var employee in employees)
    {
        Console.WriteLine($"Id: {employee.Id}, Name: {employee.Name}, Salary: {employee.Salary}");
    }
}

এখানে, MAX(e2.salary) একটি সাব-কুয়েরি যা প্রতিটি Department এর সর্বোচ্চ বেতন প্রদানকারী কর্মীটির বেতন নির্ধারণ করে এবং সেই বেতন অপেক্ষা কম বেতন প্রাপ্ত কর্মীদের নির্বাচন করা হয়। এই কুয়েরিতে Nested Subquery ব্যবহৃত হয়েছে, যেখানে একটি সাব-কুয়েরি অন্য একটি কুয়েরির অংশ হিসেবে কাজ করছে।

Complex Query with Multiple Joins

ধরা যাক, আপনি একটি কুয়েরি তৈরি করতে চান যেখানে Employee এবং Department টেবিলের মধ্যে জয়েন (join) রয়েছে এবং সেই সঙ্গে বয়স, বেতন এবং ডিপার্টমেন্টের নামের শর্ত অনুসারে ডেটা বের করতে হবে। NHibernate-এ এই কুয়েরি তৈরি করতে আমরা HQL ব্যবহার করতে পারি।

using (ISession session = sessionFactory.OpenSession())
{
    var query = session.CreateQuery(
        "SELECT e FROM Employee e " +
        "JOIN e.department d " +
        "WHERE e.age > :age AND e.salary > :salary AND d.name = :deptName"
    )
    .SetParameter("age", 30)
    .SetParameter("salary", 50000)
    .SetParameter("deptName", "IT");

    var employees = query.List<Employee>();

    foreach (var employee in employees)
    {
        Console.WriteLine($"Id: {employee.Id}, Name: {employee.Name}, Department: {employee.Department.Name}, Salary: {employee.Salary}");
    }
}

এখানে, JOIN e.department d অংশটি Employee এবং Department টেবিলের মধ্যে একটি জয়েন তৈরি করছে। তারপর আমরা বয়স, বেতন এবং ডিপার্টমেন্টের নামের শর্তগুলি প্রয়োগ করছি।

Native SQL ব্যবহার করে Complex Query

যখন আপনাকে আরও জটিল বা বিশেষ ধরনের SQL কুয়েরি প্রয়োগ করতে হয়, তখন Native SQL ব্যবহার করা হয়। NHibernate-এ Native SQL কুয়েরি চালানোর জন্য CreateSQLQuery মেথড ব্যবহার করা হয়।

Native SQL with Nested Subquery Example:

ধরা যাক, আপনি এমন একটি কুয়েরি তৈরি করতে চান যা Employee টেবিল থেকে এমন কর্মীদের নির্বাচন করবে, যাদের বেতন তাদের Department এর সর্বোচ্চ বেতন থেকে কম। Native SQL এর মাধ্যমে এই কুয়েরি চালানো যাবে:

using (ISession session = sessionFactory.OpenSession())
{
    var query = session.CreateSQLQuery(
        "SELECT e.Id, e.Name, e.Age, e.Salary " +
        "FROM Employee e " +
        "WHERE e.Salary < (" +
        "SELECT MAX(e2.Salary) " +
        "FROM Employee e2 " +
        "WHERE e2.DepartmentId = e.DepartmentId)"
    );

    var result = query.List<object[]>();

    foreach (var row in result)
    {
        Console.WriteLine($"Id: {row[0]}, Name: {row[1]}, Salary: {row[3]}");
    }
}

এখানে, একটি Native SQL কুয়েরি ব্যবহার করা হয়েছে যাতে Employee টেবিলের এমন কর্মীদের বেতন পাওয়া যায়, যাদের বেতন তাদের Department এর সর্বোচ্চ বেতন অপেক্ষা কম।

Conclusion

Complex Queries এবং Nested Subqueries হল NHibernate-এ জটিল ডেটাবেস অপারেশন করার শক্তিশালী কৌশল। HQL বা Native SQL এর মাধ্যমে আপনি এই ধরনের কুয়েরি তৈরি করতে পারেন, যেখানে একাধিক শর্ত, জয়েন এবং সাব-কুয়েরি ব্যবহৃত হয়। এই কৌশলগুলি ব্যবহার করে আপনি বড় আকারের ডেটাবেসে জটিল ডেটা অনুসন্ধান এবং ব্যবস্থাপনা করতে পারবেন।

Aggregation এবং Group By হল SQL এর গুরুত্বপূর্ণ কৌশল, যা ডেটাবেসে ডেটা সংগ্রহ, বিশ্লেষণ এবং গ্রুপিং করার জন্য ব্যবহৃত হয়। NHibernate তে আপনি SQL কুয়েরির মাধ্যমে Aggregation (যেমন, COUNT, SUM, AVG) এবং Group By কৌশল ব্যবহার করতে পারেন, যেগুলি খুবই কার্যকরী যখন আপনাকে ডেটার সারাংশ বা গ্রুপিং তথ্য বের করতে হয়।

NHibernate তে Aggregation এবং Group By ব্যবহারের জন্য আপনাকে HQL (Hibernate Query Language) বা Criteria API ব্যবহার করতে হবে। এখানে আমরা HQL এবং Native SQL উভয়ের মাধ্যমে এই কৌশলগুলো ব্যবহার করার উদাহরণ দেখব।

1. Aggregation Functions (অ্যাগ্রিগেশন ফাংশন)

Aggregation Functions হল SQL ফাংশন যা একাধিক রেকর্ডের উপর গণনা বা হিসাব করতে ব্যবহৃত হয়। NHibernate এ এই ধরনের ফাংশন ব্যবহার করতে পারেন HQL বা Criteria দিয়ে।

Aggregation Functions এর মধ্যে কিছু সাধারণ ফাংশন হলো:

• COUNT(): নির্দিষ্ট শর্ত পূর্ণ করা রেকর্ডের সংখ্যা
• SUM(): নির্দিষ্ট কলামের মানের যোগফল
• AVG(): নির্দিষ্ট কলামের মানের গড়
• MIN(): নির্দিষ্ট কলামের সর্বনিম্ন মান
• MAX(): নির্দিষ্ট কলামের সর্বোচ্চ মান

2. Aggregation ব্যবহার HQL দিয়ে

2.1 COUNT() ব্যবহার করে

ধরা যাক, আমাদের কাছে Employee নামে একটি টেবিল রয়েছে এবং আমরা দেখতে চাই কতটি Employee রেকর্ড রয়েছে।

using (var session = sessionFactory.OpenSession())
{
    var query = session.CreateQuery("select count(e) from Employee e");
    var employeeCount = query.UniqueResult<long>(); // COUNT() ফলাফল

    Console.WriteLine($"Total Employees: {employeeCount}");
}

এখানে:

• count(e) ফাংশনটি Employee টেবিলের মোট রেকর্ডের সংখ্যা গণনা করছে।

2.2 SUM() ব্যবহার করে

ধরা যাক, আপনি জানাতে চান মোট Salary (বেতন) কত, তাহলে আপনি SUM() ফাংশন ব্যবহার করতে পারেন।

using (var session = sessionFactory.OpenSession())
{
    var query = session.CreateQuery("select sum(e.salary) from Employee e");
    var totalSalary = query.UniqueResult<decimal>(); // SUM() ফলাফল

    Console.WriteLine($"Total Salary: {totalSalary}");
}

এখানে:

• sum(e.salary) ফাংশনটি সব Employee রেকর্ডের salary এর যোগফল বের করছে।

3. Group By ব্যবহার HQL দিয়ে

Group By হল SQL এর একটি কৌশল যা ডেটাকে কিছু নির্দিষ্ট কলামের ভিত্তিতে গ্রুপ করে। NHibernate এর HQL এ Group By ব্যবহার করা খুবই সহজ।

3.1 Group By ব্যবহার

ধরা যাক, আপনি Department অনুসারে Employee গুলির গড় বেতন দেখতে চান।

using (var session = sessionFactory.OpenSession())
{
    var query = session.CreateQuery("select e.department, avg(e.salary) from Employee e group by e.department");
    var result = query.List<object[]>();  // Each result will be an object array with department and average salary

    foreach (var row in result)
    {
        var department = row[0].ToString();
        var avgSalary = Convert.ToDecimal(row[1]);
        Console.WriteLine($"Department: {department}, Average Salary: {avgSalary}");
    }
}

এখানে:

• group by e.department দিয়ে Employee গুলিকে Department এর ভিত্তিতে গ্রুপ করা হয়েছে এবং প্রতি ডিপার্টমেন্টের গড় বেতন (AVG) বের করা হয়েছে।
• avg(e.salary) হল অ্যাগ্রিগেশন ফাংশন যা গড় বেতন গণনা করছে।

4. Native SQL ব্যবহার Aggregation এবং Group By

NHibernate তে আপনি Native SQL কুয়েরি দিয়ে সরাসরি SQL রেজাল্ট ও Aggregation ফাংশন ব্যবহার করতে পারেন। নিচে একটি উদাহরণ দেওয়া হলো যেখানে Native SQL ব্যবহার করে Group By এবং Aggregation করা হচ্ছে।

4.1 Native SQL Query দিয়ে Group By এবং Aggregation

ধরা যাক, আমরা Employee টেবিল থেকে Department অনুসারে Salary এর গড় এবং মোট সংখ্যা জানতে চাই।

using (var session = sessionFactory.OpenSession())
{
    var sqlQuery = session.CreateSQLQuery("SELECT e.Department, AVG(e.Salary), COUNT(e.EmployeeId) FROM Employee e GROUP BY e.Department");
    var result = sqlQuery.List<object[]>();  // List of results where each item is an array of values

    foreach (var row in result)
    {
        var department = row[0].ToString();
        var avgSalary = Convert.ToDecimal(row[1]);
        var employeeCount = Convert.ToInt32(row[2]);
        
        Console.WriteLine($"Department: {department}, Average Salary: {avgSalary}, Employee Count: {employeeCount}");
    }
}

এখানে:

• Native SQL কুয়েরি চালিয়ে আমরা Department অনুসারে Salary এর গড় (AVG) এবং কর্মচারীদের মোট সংখ্যা (COUNT) বের করেছি।
• SELECT e.Department, AVG(e.Salary), COUNT(e.EmployeeId) হল SQL কুয়েরির অংশ যেখানে GROUP BY ব্যবহার করা হয়েছে।

5. Criteria API ব্যবহার Aggregation এবং Group By

NHibernate এর Criteria API ব্যবহার করে আপনি অ্যাগ্রিগেশন এবং গ্রুপিং ফিচারও ব্যবহার করতে পারেন। এটি টেপলেট কৌশল, যা কমপ্লেক্স কুয়েরি তৈরি করতে সাহায্য করে।

5.1 Criteria API দিয়ে Aggregation

using (var session = sessionFactory.OpenSession())
{
    var criteria = session.CreateCriteria<Employee>()
                          .SetProjection(Projections.ProjectionList()
                                                   .Add(Projections.Count("EmployeeId"))
                                                   .Add(Projections.Sum("Salary"))
                          );

    var result = criteria.List<object[]>();

    foreach (var row in result)
    {
        var employeeCount = Convert.ToInt32(row[0]);
        var totalSalary = Convert.ToDecimal(row[1]);
        Console.WriteLine($"Total Employees: {employeeCount}, Total Salary: {totalSalary}");
    }
}

এখানে:

• Criteria API ব্যবহার করে COUNT() এবং SUM() ফাংশনগুলো ব্যবহার করা হয়েছে। Projections.Count এবং Projections.Sum ব্যবহার করা হয়েছে অ্যাগ্রিগেশন ফাংশন হিসেবে।

5.2 Group By Criteria API দিয়ে

using (var session = sessionFactory.OpenSession())
{
    var criteria = session.CreateCriteria<Employee>()
                          .SetProjection(Projections.ProjectionList()
                                                   .Add(Projections.GroupProperty("Department"))
                                                   .Add(Projections.Avg("Salary"))
                          );

    var result = criteria.List<object[]>();

    foreach (var row in result)
    {
        var department = row[0].ToString();
        var avgSalary = Convert.ToDecimal(row[1]);
        Console.WriteLine($"Department: {department}, Average Salary: {avgSalary}");
    }
}

এখানে:

• Projections.GroupProperty("Department") ব্যবহার করে Group By করা হয়েছে এবং AVG() ব্যবহার করে Salary এর গড় বের করা হয়েছে।

সারাংশ

Aggregation এবং Group By হল শক্তিশালী কৌশল যা আপনাকে ডেটাবেসের বিশাল পরিমাণ ডেটার উপর বিভিন্ন পরিসংখ্যান বের করতে সাহায্য করে। NHibernate এ HQL, Native SQL, এবং Criteria API ব্যবহার করে আপনি সহজেই অ্যাগ্রিগেশন ফাংশন এবং গ্রুপিং ফিচার ব্যবহার করতে পারেন। এই কৌশলগুলি ডেটা বিশ্লেষণ এবং রিপোর্ট তৈরির জন্য খুবই কার্যকরী।

NHibernate-এ Criteria API এবং Projections ব্যবহার করে আপনি অত্যন্ত জটিল কুয়েরি গঠন করতে পারেন যা SQL এর জটিলতা ছাড়াই ডেটাবেস থেকে প্রয়োজনীয় তথ্য অনুসন্ধান করতে সাহায্য করে। Criteria API একটি উচ্চমানের কুয়েরি ল্যাঙ্গুয়েজ প্রদান করে যা SQL-লাইক প্রশ্নের জন্য প্রোগ্রাম্যাটিক পদ্ধতি ব্যবহার করতে সক্ষম হয়, এবং Projections ব্যবহার করে আপনি ডেটা সিলেক্ট করতে পারেন, গ্রুপ করতে পারেন এবং কাস্টম আউটপুট তৈরি করতে পারেন।

Criteria API এর ধারণা

Criteria API একটি Object-Oriented পদ্ধতি যা SQL কুয়েরি লেখার পরিবর্তে কোড-ভিত্তিক উপায়ে ডেটাবেস কুয়েরি তৈরি করতে দেয়। এতে আপনি ডেটাবেসে কীভাবে ডেটা অনুসন্ধান করা হবে তা খুবই সহজভাবে ডিফাইন করতে পারেন, যেমন প্রোপার্টি, ক্লাস, এবং তাদের মধ্যে সম্পর্ক ব্যবহার করে।

এটি Hibernate ও NHibernate উভয়েই সমর্থিত, এবং Criteria কুয়েরি ব্যবহার করে আপনি বিভিন্ন ধরণের জটিল কুয়েরি তৈরি করতে পারেন।

Projections ব্যবহার করা

Projections মূলত NHibernate-এর Criteria API এর একটি শক্তিশালী বৈশিষ্ট্য যা আপনাকে নির্বাচনকৃত কলাম বা এগ্রিগেট ফাংশন (যেমন COUNT, AVG, SUM ইত্যাদি) থেকে ডেটা সংগ্রহ করতে সহায়তা করে। এটি যখন একটি Projection তৈরি হয় তখন SQL কুয়েরি সংক্ষেপিত হয়ে যায় এবং আপনি খুব সহজে চাহিদা অনুযায়ী আউটপুট পাবেন।

Complex Query উদাহরণ

ধরা যাক আমাদের কাছে Employee এবং Department নামের দুটি ক্লাস রয়েছে। Employee ক্লাসের মধ্যে Department সম্পর্ক রয়েছে। এবং আমরা একটি জটিল কুয়েরি তৈরি করতে চাই, যেখানে আমাদের Employee এর নাম, তার বেতন এবং সংশ্লিষ্ট Department এর নাম প্রয়োজন।

Step 1: Employee এবং Department ক্লাস

public class Employee
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual decimal Salary { get; set; }
    public virtual Department Department { get; set; }
}

public class Department
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

এখানে Employee এবং Department ক্লাসের মধ্যে একে অপরের সাথে সম্পর্ক রয়েছে, যেখানে Employee এর Department প্রপার্টি ব্যবহার করে সংশ্লিষ্ট বিভাগের তথ্য পাওয়া যাবে।

Step 2: Criteria API ব্যবহার করে Complex Query তৈরি করা

এখন, আমরা Criteria API ব্যবহার করে একটি কুয়েরি তৈরি করব যা Employee এর নাম, বেতন এবং সংশ্লিষ্ট Department এর নাম একত্রিত করে।

using NHibernate;
using NHibernate.Criterion;

public class EmployeeService
{
    public IList<Employee> GetEmployeesWithDepartmentName(decimal salaryThreshold)
    {
        using (var session = sessionFactory.OpenSession())
        {
            var criteria = session.CreateCriteria<Employee>()
                .CreateAlias("Department", "d")  // Department টেবিলের সাথে যোগদান
                .Add(Restrictions.Gt("Salary", salaryThreshold))  // Salary এর সীমা নির্ধারণ
                .SetProjection(Projections.ProjectionList()
                    .Add(Projections.Property("Name"), "EmployeeName")  // Employee এর নাম নির্বাচন
                    .Add(Projections.Property("Salary"), "EmployeeSalary")  // Employee এর Salary নির্বাচন
                    .Add(Projections.Property("d.Name"), "DepartmentName")  // Department এর নাম নির্বাচন
                )
                .List<object[]>();  // এখানে result হিসাবে object array পাওয়া যাবে

            // ফলাফল হিসেবে `object[]` এর মাধ্যমে নাম, বেতন এবং বিভাগের নাম ফেরত হবে
            return criteria.List<object[]>().Select(x => new Employee
            {
                Name = (string)x[0],
                Salary = (decimal)x[1],
                Department = new Department { Name = (string)x[2] }
            }).ToList();
        }
    }
}

এখানে:

• CreateAlias মেথড ব্যবহার করে Employee এবং Department টেবিলের মধ্যে যোগদান (JOIN) তৈরি করা হয়েছে।
• Restrictions.Gt দ্বারা Salary এর জন্য একটি শর্ত যুক্ত করা হয়েছে, যাতে নির্দিষ্ট পরিমাণ বেতনের বেশি চাকরিজীবীদের তথ্য ফেরত আসে।
• SetProjection ব্যবহার করে শুধুমাত্র নির্দিষ্ট প্রোপার্টি (যেমন নাম, বেতন, বিভাগ) নির্বাচন করা হয়েছে।
• Projections.Property ব্যবহার করে Employee এবং Department উভয়ের প্রোপার্টি নির্বাচিত করা হয়েছে।
• কুয়েরির ফলাফল object[] আকারে আসে, যেখানে প্রতিটি উপাদান হলো একটি Employee এর তথ্য এবং তার সম্পর্কিত Department এর নাম।

Step 3: Aggregate Functions ব্যবহার করা

Criteria API ব্যবহার করে আপনি এগ্রিগেট ফাংশন (যেমন COUNT, SUM, AVG) ব্যবহার করতে পারেন। নিচে SUM ফাংশন ব্যবহার করে একটি কুয়েরি তৈরি করা হয়েছে, যেখানে সমস্ত Employee এর বেতন যোগফল বের করা হবে।

using NHibernate;
using NHibernate.Criterion;

public class EmployeeService
{
    public decimal GetTotalSalary()
    {
        using (var session = sessionFactory.OpenSession())
        {
            var criteria = session.CreateCriteria<Employee>()
                .SetProjection(Projections.Sum("Salary"));  // SUM ফাংশন ব্যবহার করে Salary এর মোট যোগফল বের করা

            return criteria.UniqueResult<decimal>();  // একক ফলাফল ফেরত হবে
        }
    }
}

এখানে:

• Projections.Sum ব্যবহার করে সমস্ত Employee এর বেতন (Salary) এর যোগফল হিসাব করা হয়েছে।
• UniqueResult ব্যবহার করে একক রেজাল্ট ফেরত পাওয়া যাবে।

Step 4: Grouping এবং Filtering

Criteria API ব্যবহার করে আপনি GROUP BY এবং HAVING ক্লজও প্রয়োগ করতে পারেন। নিচে Group By এবং Having শর্তসহ একটি কুয়েরি উদাহরণ দেয়া হলো:

using NHibernate;
using NHibernate.Criterion;

public class EmployeeService
{
    public IList<object[]> GetDepartmentWiseSalarySummary()
    {
        using (var session = sessionFactory.OpenSession())
        {
            var criteria = session.CreateCriteria<Employee>()
                .CreateAlias("Department", "d")  // Department টেবিলের সাথে যোগদান
                .SetProjection(Projections.ProjectionList()
                    .Add(Projections.GroupProperty("d.Name"), "DepartmentName")  // Department এর নাম গ্রুপ করা
                    .Add(Projections.Sum("Salary"), "TotalSalary")  // Salary এর যোগফল
                )
                .Add(Restrictions.Gt("Salary", 30000))  // Salary এর শর্ত
                .List<object[]>();  // ফলাফল হিসেবে object array ফেরত

            return criteria.List<object[]>();
        }
    }
}

এখানে:

• Projections.GroupProperty ব্যবহার করে বিভাগ অনুযায়ী গ্রুপিং করা হয়েছে।
• Projections.Sum দ্বারা প্রতিটি বিভাগের জন্য মোট বেতন যোগফল বের করা হয়েছে।

উপসংহার

NHibernate এর Criteria API এবং Projections ব্যবহার করে আপনি খুব সহজে জটিল কুয়েরি তৈরি করতে পারেন, যেমন একাধিক টেবিলের মধ্যে সম্পর্ক তৈরি করা, ডেটা গ্রুপিং, এবং এগ্রিগেট ফাংশন ব্যবহার করা। এই পদ্ধতিগুলি SQL কুয়েরি লেখার চেয়ে অনেক বেশি শক্তিশালী এবং ফ্লেক্সিবল, কারণ এটি একটি প্রোগ্রাম্যাটিক পদ্ধতিতে কাজ করে, যা কোডের মাধ্যমে আপনার কাঙ্ক্ষিত ফলাফল অর্জন করতে সক্ষম।

NHibernate তে Scalar Queries এবং ResultTransformer দুটি গুরুত্বপূর্ণ কৌশল যা আপনাকে কমপ্লেক্স ডেটাবেস কুয়েরি থেকে একক বা সীমিত ধরনের ডেটা বের করতে এবং কাস্টম অবজেক্টে রূপান্তর করতে সাহায্য করে। এদের ব্যবহারের মাধ্যমে, আপনি ডেটাবেস থেকে শুধুমাত্র প্রয়োজনীয় ডেটা আহরণ করতে পারেন, যা আপনার অ্যাপ্লিকেশনের পারফরম্যান্স বৃদ্ধি করবে।

Scalar Query কী?

Scalar Query হল এমন একটি কুয়েরি, যা শুধুমাত্র একটি একক মান বা ছোট পরিমাণ ডেটা রিটার্ন করে, যেমন একটি সংখ্যা, একটি স্ট্রিং বা অন্যান্য কোন একক ভ্যালু। সাধারণত, Scalar Query গুলো ব্যবহার করা হয় যখন আপনাকে শুধুমাত্র একটি নির্দিষ্ট কলামের বা কিছু হিসাবী ফলাফল (যেমন COUNT, SUM, MAX, ইত্যাদি) প্রয়োজন।

NHibernate তে Scalar Query এক্সিকিউট করতে আপনি সাধারণত CreateQuery বা CreateSQLQuery মেথড ব্যবহার করেন।

Scalar Query উদাহরণ

ধরা যাক, আমরা Employee টেবিল থেকে মোট কর্মচারীর সংখ্যা বের করতে চাই।

using (ISession session = sessionFactory.OpenSession())
{
    string hql = "SELECT COUNT(e) FROM Employee e";
    var result = session.CreateQuery(hql).UniqueResult<int>();
    Console.WriteLine($"Total number of employees: {result}");
}

এখানে:

• UniqueResult<int>() মেথডটি Scalar Query এর ফলাফল হিসেবে একটি একক মান (এই ক্ষেত্রে, একটি সংখ্যা) ফেরত দিবে।
• COUNT(e) SQL ফাংশন ব্যবহার করা হয়েছে, যা মোট কর্মচারীর সংখ্যা হিসাব করবে।

এটি একটি সাধারিত Scalar Query উদাহরণ যা শুধু একটি একক ফলাফল ফেরত দেয়।

ResultTransformer Techniques

ResultTransformer হল একটি NHibernate এর টুল, যা SQL বা HQL কুয়েরির ফলাফলকে একটি কাস্টম অবজেক্টে রূপান্তর করতে ব্যবহৃত হয়। ResultTransformer একটি অত্যন্ত শক্তিশালী কৌশল, যেটি আপনাকে SQL কুয়েরির ফলাফলকে আপনার নির্দিষ্ট ডোমেইন অবজেক্টে, DTO (Data Transfer Object) বা ডিকশনারি আকারে রূপান্তর করতে সাহায্য করে।

ResultTransformer এর প্রধান ব্যবহার

• AliasToBean: এটি SQL বা HQL কুয়েরির ফলাফলকে নির্দিষ্ট DTO ক্লাসে রূপান্তরিত করে।
• AliasToEntityMap: এটি কুয়েরির ফলাফলকে একটি Dictionary<string, object> আকারে রূপান্তর করে, যেখানে কলাম নামগুলো কী হিসেবে এবং তাদের মানগুলো ভ্যালু হিসেবে থাকে।
• Transformers.AliasToList: একাধিক ফলাফল একত্রে একটি তালিকায় রূপান্তর করে।

ResultTransformer এর সাহায্যে Scalar Query এর রেজাল্ট রূপান্তর করা

ধরা যাক, আমরা Employee এবং Department এর সম্পর্কের মধ্যে ডেটা সংগ্রহ করতে চাই এবং আমরা সেই ডেটা কাস্টম DTO ক্লাসে রূপান্তর করতে চাই।

1. DTO (Data Transfer Object) ক্লাস তৈরি

public class EmployeeDepartmentDTO
{
    public virtual int EmployeeId { get; set; }
    public virtual string EmployeeName { get; set; }
    public virtual string DepartmentName { get; set; }
}

2. HQL Scalar Query এবং ResultTransformer ব্যবহার

using (ISession session = sessionFactory.OpenSession())
{
    string hql = @"
        SELECT e.EmployeeId AS EmployeeId, e.Name AS EmployeeName, d.Name AS DepartmentName
        FROM Employee e
        JOIN e.Department d";
    
    var query = session.CreateQuery(hql);

    // ResultTransformer এর মাধ্যমে রেজাল্ট DTO তে রূপান্তর করা
    query.SetResultTransformer(Transformers.AliasToBean<EmployeeDepartmentDTO>());

    var result = query.List<EmployeeDepartmentDTO>();

    foreach (var item in result)
    {
        Console.WriteLine($"Employee: {item.EmployeeName}, Department: {item.DepartmentName}");
    }
}

এখানে:

• AliasToBean ব্যবহার করা হয়েছে, যাতে SQL কুয়েরির ফলাফল সরাসরি EmployeeDepartmentDTO ক্লাসে রূপান্তরিত হয়।
• HQL কুয়েরি ব্যবহার করা হয়েছে, যা Employee এবং Department এর নাম (যেমন e.Name এবং d.Name) রিটার্ন করছে।

এই কৌশলটি বিশেষভাবে উপকারী যখন আপনি SQL বা HQL কুয়েরির ফলাফলকে DTO বা অন্য কাস্টম ক্লাসে রূপান্তর করতে চান, যাতে তা ব্যবহারকারী ইন্টারফেসে প্রদর্শন করা বা অন্যান্য প্রয়োজনে ব্যবহার করা যায়।

Scalar Query এর মাধ্যমে Complex Aggregation

ধরা যাক, আপনি Employee টেবিল থেকে বিভাগের ভিত্তিতে মোট কর্মচারীর সংখ্যা এবং তাদের গড় বয়স বের করতে চান।

using (ISession session = sessionFactory.OpenSession())
{
    string hql = @"
        SELECT d.Name AS DepartmentName, COUNT(e.EmployeeId) AS TotalEmployees, AVG(e.Age) AS AverageAge
        FROM Employee e
        JOIN e.Department d
        GROUP BY d.Name";
    
    var query = session.CreateQuery(hql);
    
    // Scalar result fetching
    var result = query.List<object[]>();

    foreach (var row in result)
    {
        string departmentName = (string)row[0];
        int totalEmployees = (int)row[1];
        double averageAge = (double)row[2];

        Console.WriteLine($"Department: {departmentName}, Total Employees: {totalEmployees}, Average Age: {averageAge}");
    }
}

এখানে:

• object[] ব্যবহার করা হয়েছে, কারণ কুয়েরি একাধিক কলাম রিটার্ন করছে।
• COUNT এবং AVG ফাংশন ব্যবহার করা হয়েছে যেগুলি অ্যাগ্রিগেট রেজাল্ট প্রদান করে।

এটি Scalar Query এর মাধ্যমে একাধিক অ্যাগ্রিগেটেড ফলাফল সংগ্রহের একটি উদাহরণ।

ResultTransformer এর অন্যান্য ধরন

NHibernate তে আরও কিছু সাধারণ ResultTransformer আছে, যেগুলি নির্দিষ্ট প্রয়োজন অনুযায়ী ব্যবহার করা হয়:

1. AliasToEntityMap

এটি SQL কুয়েরির ফলাফলকে একটি Dictionary<string, object> তে রূপান্তর করে।

var query = session.CreateQuery("SELECT e.EmployeeId, e.Name FROM Employee e");
query.SetResultTransformer(Transformers.AliasToEntityMap());

var result = query.List();
foreach (var row in result)
{
    var map = (IDictionary<string, object>)row;
    Console.WriteLine($"EmployeeId: {map["EmployeeId"]}, Name: {map["Name"]}");
}

2. AliasToList

একাধিক রেজাল্ট (অথবা কলাম) একটি তালিকায় রূপান্তর করে।

var query = session.CreateQuery("SELECT e.Name, e.Age FROM Employee e");
query.SetResultTransformer(Transformers.AliasToList());

var result = query.List();
foreach (var row in result)
{
    var list = (IList<object>)row;
    Console.WriteLine($"Name: {list[0]}, Age: {list[1]}");
}

উপসংহার

Scalar Queries এবং ResultTransformers ব্যবহার করে আপনি NHibernate এ কাস্টম ডেটা রিট্রিভাল এবং রূপান্তর অত্যন্ত সহজভাবে করতে পারেন। Scalar Query তে আপনি একক বা অ্যাগ্রিগেটেড ডেটা উদ্ধার করতে পারেন এবং ResultTransformer এর মাধ্যমে আপনি সেই ডেটাকে আপনার প্রয়োজনীয় অবজেক্টে রূপান্তর করতে পারবেন। এর ফলে, আপনি অধিক নমনীয়তা এবং পারফরম্যান্স পাচ্ছেন যখন আপনি ডেটাবেস থেকে কাস্টম ডেটা সংগ্রহ এবং রূপান্তর করেন।