Tech
Forums
Jobs
Books
Events
Live
Interviews Prep
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
2
Reply
Performing Multiple Joins in EF
Fares Ayyad
Aug 19 2016 9:33 AM
259
Reply
Hello everyone
i want to perform joins in EF from SQL statement but, i tried to but i stopped because i feel that there's somthing wrong.
this is SQL statement:
Select
i.cdin_cdindexid,p.pinv_PerformaInvID,
coalesce
(i.cdin_serial,0)
as
depno
,
coalesce
(
convert
(datetime,
left
(
convert
(nvarchar,i.cdin_startunstufdate,120),10),120),
'-'
)
as
deidate,
coalesce
(i.cdin_goodsDesc,
'-'
)
as
gooddesc ,
coalesce
(i.cdin_Customdeclar,
'-'
)
as
custdec
,
coalesce
(i.cdin_NoofPackages,0)
as
pkg,
coalesce
(i.cdin_WT,0)
as
wt ,
coalesce
(i.cdin_volumewt,0)
as
vwt ,
coalesce
(i.cdin_MortgageAmount,0)
as
lcamt,
coalesce
(p.pinv_name,
'-'
)
as
invno,
coalesce
(p.pinv_TotalAmount,0)
as
invamt,p.pinv_Status,p.pinv_InvoiceProperty
as
prop
,
coalesce
(c.comp_name,
'-'
)
as
custname,
coalesce
(Comp_CompanyId,
'-'
)
as
custid ,
coalesce
(c.comp_idcust,
'-'
)
as
accpacno,
coalesce
(t.Terr_Caption,
'-'
)
as
Terr,
convert
(nvarchar,
'01'
,2)
as
type
from
cdindex i
inner
join
company c
on
i.cdin_CompanyId =c.Comp_CompanyId
inner
join
Territories t
on
i.cdin_Secterr =t.Terr_TerritoryID
left
outer
join
PerformaInv p
on
i.cdin_cdindexid=p.pinv_CDIndexId
where
(cdin_deleted
Is
null
And
c.comp_deleted
Is
null
And
t.Terr_Deleted
Is
null
And
p.pinv_deleted
Is
null
)
and
cdin_startunstufdate
between
'2016-06-01'
and
'2016-07-28'
and
(p.pinv_status
in
(
'Draft'
,
'Posted'
)
or
pinv_status
is
null
)
and
(p.pinv_InvoiceProperty =
'01'
or
p.pinv_InvoiceProperty
is
null
)
This is the code i tried to type in EF 6.0/C#
(from i
in
db.CDIndexes
join c
in
db.Companies on i.cdin_CompanyId equals c.Comp_CompanyId
join t
in
db.Territories on i.cdin_Secterr equals t.Terr_TerritoryID
join p
in
db.PerformaInvs.DefaultIfEmpty() on i.cdin_CDIndexID equals p.pinv_CDIndexId
where (i.cdin_startunstufdate>=
new
DateTime(2016 - 06 - 01) && i.cdin_startunstufdate>=
new
DateTime(2016-06-28) &&
p.pinv_Status
is
'Draft'
|| p.pinv_Status==
'Posted'
)
i have a big issue with implementing leftouter join and the where cluase conditions.
can anyone help me with this code
Or if the sql select statement is complex like that is there any way to code an application with EF.
thank you all
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
2
)
Next Recommended Forum
Mvc connection with ado .net connectivity
WCF binding Entity and a WPF client