Pages

Google Analytics Alternative

Search This Blog

Thursday, October 25, 2012

EntityFramework inner join

Column concatenation using Linq to EntityFramework.
here we have one simple example where there are two tables.
one
Employee with following fileds

CREATE TABLE [dbo].[empMaster](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Age] [int] NULL,
[IsActive] [bit] NULL CONSTRAINT [DF_empMaster_IsActive]  DEFAULT ((1)),
[CreatedDate] [datetime] NULL CONSTRAINT [DF_empMaster_CreatedDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_empMaster] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


and another table is with

CREATE TABLE [dbo].[employee](
[id] [int] IDENTITY(1,1) NOT NULL,
[Salary] [int] NULL,
 CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]







Now i want to get name of employee and salary of that employee.
so i used innerjoin like this

select em.id,em.name,e.salary
from empMaster em
inner join employee e
on em.id=e.id
Result of inner join.


but in Linq To EntityFrame work it works like this.

   var outputvalue = (from emp in obj.empMasters 
                                      join e in obj.employees on emp.Id equals e.id
                                     select new {
                                        emp.Name,e.Salary,emp.Id  
                                     }).ToList().Select(q =>new {Id = q.Id,Name = q.Name,dummyValue = q.Id.ToString() + " " + 
                                         q.Name.ToString()});

Thanks



Tuesday, October 16, 2012

Prepare DTO layer by using below script


DECLARE @CurID INT, @MaxID INT
Declare @sql nvarchar(MAX)
Declare @TmpTable table (ID int Identity(1,1), c_Column_name varchar(500),c_Data_type varchar(50),c_character_Maximum_length nvarchar(50))

Insert into @TmpTable (c_Column_name,c_Data_type,c_character_Maximum_length)
    SELECT COLUMN_NAME ,DATA_TYPE ,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TT_M_User'  --Table Name
ORDER BY ORDINAL_POSITION ASC;
set @CurID = 1
set @sql=''
SELECT @MaxID=Max(ID) FROM @TmpTable WHILE @MaxID >=@CurID BEGIN -- Logic to be performed
select @sql = @sql + char(13)+ ' public ' +
    case c_Data_type
    when 'varchar' then ' string '
    when 'nvarchar' then ' string '
    when 'bit' then ' bool '
    when 'datetime' then ' DateTime '
    when 'binary' then ' Byte[] '
    else c_Data_type end + ' ' + c_Column_Name + ' {get;set;} '
from @TmpTable where ID=@CurID
set @CurID = @CurID +1
END
print @sql