GROUP BY Query in LINQ TO ENTITY


Introduction:

When we create our application using LINQ, in that case we have to write all SQL queries using LINQ query operators. And it is a general requirement to write GROUP BY query when we try to build reports in our application in most cases.

CODE:

Following is the code or Query example to write GROUP BY Query in LINQ to Entities or Entity Framework:

var objData = from f in DASEntity.FILES
from fr in DASEntity.FILE_REQUEST_DETAILS
from s in DASEntity.STATUS
where f.ID == fr.FILE_ID
&& fr.STATUS_ID == s.ID
&& f.CLIENT_TOKEN == ClientToken
&& fr.CLIENT_TOKEN == ClientToken
group s by new { s.ID, s.NAME } into t
select new
{
StatusID = t.Key.ID,
Name = t.Key.NAME,
FileCount = t.Count()
};

Transaction in Linq To Entity or Entity Framework


A transaction is a series of operations performed as a single unit of work. Entity Framework transactions are a part of its internal architecture. The SaveChanges method operates within a transaction and saves results of the work. It is designed to ensure data integrity.

Imagine we want the SaveChanges to be only a part of more complicated update process. For example, we want to execute several SaveChanges as one operation in the single ObjectContext.
The possible solution is to use the database connection. Here is a sample code:

STP_TAXEntities context = new STP_TAXEntities();

DbTransaction dbTran = null;

Message = string.Empty;

try

{

if (context.Connection.State == ConnectionState.Open)

{

context.Connection.Close();

}

context.Connection.Open();

dbTran = context.Connection.BeginTransaction();

int Count = 0;

Count = context.COUNTRY_TAXQDI_ALLOWABILITY.Where(a => a.COUNTRY_CODE == CountryCode && a.CLIENT_TOKEN == ClientToken).Count();

if (Count > 0)

{

var query = from a in context.COUNTRY_TAXQDI_ALLOWABILITY

where a.COUNTRY_CODE == CountryCode &&   a.CLIENT_TOKEN == ClientToken

select a;

foreach (var item in query)

{

context.COUNTRY_TAXQDI_ALLOWABILITY.DeleteObject(item);

context.SaveChanges();

}

}

else

{

Message = “No security country exception found for selected country.”;

}

dbTran.Commit();

}

catch (Exception)

{

dbTran.Rollback();

throw;

}

finally

{

context.Connection.Close();

}

As we can easily ensure, the transaction is opened on database connection, and SaveChanges are added to this transaction.