Ado.net Entity Framework Example

Getting problem in Connectionstring in Entity framework ?
Configure the way following config setting done.

Option - 1 < add name="HRRPMSContext" providerName="System.Data.EntityClient" connectionString="metadata=res://ETG.Base.Applications.EntityFramework, Culture=neutral,PublicKeyToken=null/ HRRPMSContext.csdl|res://ETG.Base.Applications.EntityFramework, Culture=neutral,PublicKeyToken=null/ HRRPMSContext.ssdl|res://ETG.Base.Applications.EntityFramework, Culture=neutral,PublicKeyToken=null/ HRRPMSContext.msl;provider=System.Data.SqlClient;provider connection string='Data Source=ETG1\SQLEXPRESS;;Initial Catalog=ACSampleDB;User ID=HRRPMS;Password=pass;Integrated Security=True;multipleactiveresultsets=true'"/ >

Option - 2
using System.Data.Entity;
using System.Data.EntityClient;
using System.Data.SqlClient;
public static string DatabaseConnectionString
{
get
{
SqlConnectionStringBuilder providerCs = new SqlConnectionStringBuilder();
providerCs.InitialCatalog = ConfigurationSettings.AppSettings["DatabaseName"];
providerCs.UserID = ConfigurationSettings.AppSettings["UserId"];
providerCs.Password = ConfigurationSettings.AppSettings["Password"];
providerCs.DataSource = ConfigurationSettings.AppSettings["ServerName"];
providerCs.MultipleActiveResultSets = true;
providerCs.TrustServerCertificate = false;
EntityConnectionStringBuilder ecsb = new EntityConnectionStringBuilder();
ecsb.Provider = "System.Data.SqlClient";
ecsb.ProviderConnectionString = providerCs.ToString();
ecsb.Metadata = string.Format("res://{0}/BetModel.csdl|res://{0}/BetModel.ssdl|res://{0}/BetModel.msl", typeof(Entities).Assembly.FullName);
return ecsb.ToString();
}
}

In the above string ETG.Base.Applications.EntityFramework is my assembly name ETG.Base.Applications.EntityFramework.dll Fetching a collection using entity framework.
public static List< tbCandidate> GetAllCandidates()
{
List< tbCandidate > candidates = new List< tbCandidate>(); ;
using (var hrEntities = new HRRPMSContext())
{
foreach (var candidate in hrEntities.tbCandidate)
{
candidates.Add(candidate);
}
}
return candidates;
}

Adding data example :
public static void SetData()
{
tbCandidate candidate = new tbCandidate();
candidate.Name = "Arindam Chakraborty";
candidate.Phone = "11545645656";
candidate.Qualification = "B.Sc";
candidate.Street = "New Golden Nest";
Add(candidate);
}
public static void Add(tbCandidate obj)
{
using (var hrEntities = new HRRPMSContext())
{
hrEntities.AddTotbCandidate(obj);
hrEntities.SaveChanges();
}
}

Find a candidate by name from database
public static tbCandidate FindCndidateByName(string name)
{
tbCandidate candidate = null;
using (var hrEntities = new HRRPMSContext())
{
candidate = hrEntities.tbCandidate.FirstOrDefault(c => c.Name == name);
}
return candidate;
}

Update a candidate information
public static void Update(tbCandidate updatedCandidate)
{
using (var hrEntities = new HRRPMSContext())
{
tbCandidate candidate = hrEntities.tbCandidate.FirstOrDefault(c => c.CandidateID == updatedCandidate.CandidateID);
candidate = updatedCandidate;
hrEntities.tbCandidate.Context.ApplyPropertyChanges("tbCandidate", candidate);
hrEntities.tbCandidate.Context.SaveChanges();
}
}

Add Data in many tables with transaction scope
public static void AddMultipleRecord(tbInterview interview, tbRequirement requirement)
{
using (TransactionScope scope = new TransactionScope())
{
using (var hrEntities = new HRRPMSContext())
{
hrEntities.AddTotbInterview(interview);
hrEntities.AddTotbRequirement(requirement);
hrEntities.SaveChanges();
scope.Complete();
}
}
}

Sum Example using LINQ:
public string GetUserAvailableBalance(long userId)
{
decimal? userAvailableBalace = 0.0M;
using (Entities context = new Entities())
{
userAvailableBalace = context.tbUserPoint
.Where(up => up.UserId == userId)
.Sum(up => up.Amount);
}
return userAvailableBalace.ToString();
}