1.Concatenating the first name and last name?
Just add another property in your View and concatenate FirstName and LastName as a read only property , Somthing like this :
return (from c in db.Doctors select new Doctor { FullName = c.FirstName + " " + c.LastName, DoctorId = c.DoctorId }) .ToList<Doctor>(); // or just .ToList();
public class DisplayDoctor { public string FullName { get; set; } public int DoctorId { get; set; } } return (from c in db.Doctors select new DisplayDoctor { FullName = c.FirstName + " " + c.LastName, DoctorId = c.DoctorId }) .ToList<DisplayDoctor>(); // or just .ToList();
2,Left outer join in LINQ?
//left join
var result = from c in db.Customers
join o in db.Orders on c.CustomerId equals o.CustomerId into gj
from sub in gj.DefaultIfEmpty()
select new { Quantity = c.CustomerName, pet=(sub==null ? 0: sub.OrderId) };
ruleSets = (from rs in db.RuleSets
join brs in db.BatchRuleSets on rs.ID equals brs.RuleSetID into j1
from jbrs in j1.DefaultIfEmpty()
join b in db.Batches on jbrs.BatchID equals b.id into j2 from jb in j2.DefaultIfEmpty() where !clientId.HasValue || jb.ClientID == clientId.Value where !batchId.HasValue || jbrs.BatchID == batchId.Value select new { rs.ID, rs.Description, rs.IsActive, rs.CreatedDate, rs.EffectiveDate, rs.ExpirationDate, BatchName = jb.FileName, jb.ClientID }).ToList().Select(x => new { x.ID, x.Description, x.IsActive, x.CreatedDate, x.EffectiveDate, x.ExpirationDate, x.BatchName, ClientName = GetClientName(x.ClientID)});
3.Inner Join?
//inner join
//var result = from c in db.Customers
// join o in db.Orders on c.CustomerId equals o.CustomerId
// select new { Quantity = c.CustomerName, order1=o.OrderId };
class Program { static AccountContextDataContext aContext = new AccountContextDataContext(@"Data Source=;Initial Catalog=;Integrated Security=True"); static LoanContextDataContext lContext = new LoanContextDataContext(@"Data Source=;Initial Catalog=;Integrated Security=True"); static void Main() { var query = from a in aContext.ACCOUNTs join app in aContext.APPLICATIONs on a.GUID_ACCOUNT_ID equals app.GUID_ACCOUNT where app.GUID_APPLICATION.ToString() == "24551D72-D4C2-428B-84BA-5837A25D8CF6" select GetLoans(app.GUID_APPLICATION); IEnumerable<LOAN> loan = query.First(); foreach (LOAN enumerable in loan) { Console.WriteLine(enumerable.GUID_LOAN); } Console.ReadLine(); } private static IEnumerable<LOAN> GetLoans(Guid applicationGuid) { return (from l in lContext.LOANs where l.GUID_APPLICATION == applicationGuid select l).AsQueryable(); } }
Remove the Order from consturctor.
public List
We can also retrieve data or call a Stored Procedure using a SQL Connection Command and DbDataReader. The Object Context has a translate method that translates the entity data from DbDataReader into the requested type object. This method enables us to execute a standard ADO.Net query against a data source and return data rows into entity objects. Using the following code we can call a Stored Procedure and retrieve data in entity form.using (Entities context = new Entities())
{
string ConnectionString = (context.Connection as EntityConnection).StoreConnection.ConnectionString;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
builder.ConnectTimeout = 2500;
SqlConnection con = new SqlConnection(builder.ConnectionString);
System.Data.Common.DbDataReader sqlReader;
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "GetEmployeeData";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
Writing and managing ADO.Net code for data access is a tedious and monotonous job. Microsoft has provided an O/RM framework called "Entity Framework" to automate database related activities for your application.
Entity framework is an Object/Relational Mapping (O/RM) framework. It is an enhancement to ADO.NET that gives developers an automated mechanism for accessing & storing the data in the database.
Just add another property in your View and concatenate FirstName and LastName as a read only property , Somthing like this :
public string FullName
{
get { return FirstName + " " +LastName ;}
}return (from c in db.Doctors select new Doctor { FullName = c.FirstName + " " + c.LastName, DoctorId = c.DoctorId }) .ToList<Doctor>(); // or just .ToList();
public class DisplayDoctor { public string FullName { get; set; } public int DoctorId { get; set; } } return (from c in db.Doctors select new DisplayDoctor { FullName = c.FirstName + " " + c.LastName, DoctorId = c.DoctorId }) .ToList<DisplayDoctor>(); // or just .ToList();
2,Left outer join in LINQ?
//left join
var result = from c in db.Customers
join o in db.Orders on c.CustomerId equals o.CustomerId into gj
from sub in gj.DefaultIfEmpty()
select new { Quantity = c.CustomerName, pet=(sub==null ? 0: sub.OrderId) };
ruleSets = (from rs in db.RuleSets
join brs in db.BatchRuleSets on rs.ID equals brs.RuleSetID into j1
from jbrs in j1.DefaultIfEmpty()
join b in db.Batches on jbrs.BatchID equals b.id into j2 from jb in j2.DefaultIfEmpty() where !clientId.HasValue || jb.ClientID == clientId.Value where !batchId.HasValue || jbrs.BatchID == batchId.Value select new { rs.ID, rs.Description, rs.IsActive, rs.CreatedDate, rs.EffectiveDate, rs.ExpirationDate, BatchName = jb.FileName, jb.ClientID }).ToList().Select(x => new { x.ID, x.Description, x.IsActive, x.CreatedDate, x.EffectiveDate, x.ExpirationDate, x.BatchName, ClientName = GetClientName(x.ClientID)});
3.Inner Join?
//inner join
//var result = from c in db.Customers
// join o in db.Orders on c.CustomerId equals o.CustomerId
// select new { Quantity = c.CustomerName, order1=o.OrderId };
class Program { static AccountContextDataContext aContext = new AccountContextDataContext(@"Data Source=;Initial Catalog=;Integrated Security=True"); static LoanContextDataContext lContext = new LoanContextDataContext(@"Data Source=;Initial Catalog=;Integrated Security=True"); static void Main() { var query = from a in aContext.ACCOUNTs join app in aContext.APPLICATIONs on a.GUID_ACCOUNT_ID equals app.GUID_ACCOUNT where app.GUID_APPLICATION.ToString() == "24551D72-D4C2-428B-84BA-5837A25D8CF6" select GetLoans(app.GUID_APPLICATION); IEnumerable<LOAN> loan = query.First(); foreach (LOAN enumerable in loan) { Console.WriteLine(enumerable.GUID_LOAN); } Console.ReadLine(); } private static IEnumerable<LOAN> GetLoans(Guid applicationGuid) { return (from l in lContext.LOANs where l.GUID_APPLICATION == applicationGuid select l).AsQueryable(); } }
4. Lazy loading in Entity framework?
Lazy loading means delaying the loading of related data until you specifically request it. For example, Student class contains StudentAddress as complex property. So context first loads all the students from the database then it will load address of particular student when we access StudentAddress property as below.
Rules for lazy loading:
- context.Configuration.ProxyCreationEnabled should be true.
- context.Configuration.LazyLoadingEnabled should be true.
- Complex property should be defined as virtual. Context will not do lazy loading if the property is not define as virtual.
Remove the Order from consturctor.
public List
- customer cust=new customer()
- foreach(Order o1 in orders)
{
console.writeline(o1.ordernumber);//lazy loading calls now
- }
5.Calling stored procedure in Entity framework?
Right-click Stored Procedure and select "Add Function Import".
- return item is complex type
using (Entities context = new Entities())
{
IEnumerable<EmployeeDetails> empDetails = context.GetEmployeeData();
}
2nd option suing exec
// using DBContext (EF 4.1 and above)using (Entities context = new Entities())
{
IEnumerable<EmployeeDetails> empDetails = context. Database.SqlQuery
< EmployeeDetails >("exec GetEmployeeData ", null).ToList();
}
- 3rd option
We can also retrieve data or call a Stored Procedure using a SQL Connection Command and DbDataReader. The Object Context has a translate method that translates the entity data from DbDataReader into the requested type object. This method enables us to execute a standard ADO.Net query against a data source and return data rows into entity objects. Using the following code we can call a Stored Procedure and retrieve data in entity form.using (Entities context = new Entities())
{
string ConnectionString = (context.Connection as EntityConnection).StoreConnection.ConnectionString;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
builder.ConnectTimeout = 2500;
SqlConnection con = new SqlConnection(builder.ConnectionString);
System.Data.Common.DbDataReader sqlReader;
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "GetEmployeeData";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
sqlReader = (System.Data.Common.DbDataReader)cmd.ExecuteReader();
IEnumerable<EmployeeDetail> empDetails = context.Translate<EmployeeDetail>(sqlReader).ToList();
}
}
Stored procedure in EF?
http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx
6.Multiple Databases in Entity framework?
7.Entity framework advantages ?
Maintain the versioning.
8. How to enable migration EF?
Enable-Migrations -ContextTypeName AdvantureworksContext
pm>Add-Migration Initial -ignorechanges
pm>update-database
9. EDM file consist of?
SSDL Content,CSDL and C -S mapping content
i.storage model
ii.Conceptual Model
iii.Mappings
a.Entity Container
b.Entity Set
c.Entity Type
d.Association Set
e.Object context
10.Eagar loading?
Eager loading is achieved using the Include method of IQueryable.
11.What is entity framework?IEnumerable<EmployeeDetail> empDetails = context.Translate<EmployeeDetail>(sqlReader).ToList();
}
}
Stored procedure in EF?
http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx
6.Multiple Databases in Entity framework?
7.Entity framework advantages ?
Maintain the versioning.
8. How to enable migration EF?
Enable-Migrations -ContextTypeName AdvantureworksContext
pm>Add-Migration Initial -ignorechanges
pm>update-database
9. EDM file consist of?
SSDL Content,CSDL and C -S mapping content
i.storage model
ii.Conceptual Model
iii.Mappings
a.Entity Container
b.Entity Set
c.Entity Type
d.Association Set
e.Object context
10.Eagar loading?
Eager loading is achieved using the Include method of IQueryable.
using (var context = new SchoolDBEntities()) { var res = (from s in context.Students.Include("Standard") where s.StudentName == "Student1" select s).FirstOrDefault(); }
Student table and Standard table
Writing and managing ADO.Net code for data access is a tedious and monotonous job. Microsoft has provided an O/RM framework called "Entity Framework" to automate database related activities for your application.
Entity framework is an Object/Relational Mapping (O/RM) framework. It is an enhancement to ADO.NET that gives developers an automated mechanism for accessing & storing the data in the database.
