Click here to Skip to main content
14,644,614 members
Rate this:
Please Sign up or sign in to vote.
See more: , +
I have a SQL query that I want to write in using Linq I know that I should use join but I have difficulty how to write this epecially
Cananybody Help me writing this Linq query?


Quote:
dbo.CT ON dbo.VE.SM = dbo.CT.Tkt OR dbo.VDA.SMSe = dbo.CT.Tkt INNER JOIN


This is my SQL Query:

SELECT
    dbo.VE.X,
    dbo.VE.Ope,
    dbo.VE.Ac
FROM dbo.VE
INNER JOIN dbo.VDA ON dbo.VE.X = dbo.VDA.X
INNER JOIN dbo.CT ON
    dbo.VE.SM = dbo.CT.Tkt OR
    dbo.VDA.SMSe = dbo.CT.Tkt
INNER JOIN dbo.LM ON dbo.CT.Mkt = dbo.LM.MarketEsigi
where
    dbo.VDA.Time >='' and
    dbo.VDA.Time <='' and
    dbo.VDA.MCode = ''
Posted
Updated 17-Jul-15 9:37am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Hello, have you used the let statement in the query? This should solve the problem. Let me create simple example for you.

This should be it:

IQueryable<VE> ve = new List<VE>()
    {
        new VE()
            {
                Ac = "Ac1",
                Ope = "Ope1",
                SM = "SM1",
                X = "X1"
            },
        new VE()
            {
                Ac = "X1",
                Ope = "Ope2",
                SM = "SM2",
                X="X2"
            },
        new VE()
            {
                Ac = "X1",
                Ope = "Ope2",
                SM = "SM2",
                X="X2"
            },
        new VE()
            {
                Ac = "X1",
                Ope = "Ope2",
                SM = "SM2",
                X="X2"
            }
    }.AsQueryable();

IQueryable<VDA> vda = new List<VDA>()
    {
        new VDA()
            {
                MCode = "MCode1",
                SMSe = "SM2",
                Time = "Time1",
                X="X1"
            },
        new VDA()
            {
                MCode = "MCode1",
                SMSe = "SMSe1",
                Time = "Time2",
                X="X2"
            }
    }.AsQueryable();

IQueryable<CT> ct = new List<CT>()
    {
        new CT()
            {
                MCode = "MCode1",
                Time = "Time1",
                Mkt = "Mkt1",
                Tkt = "SM1"
            },
        new CT()
            {
                MCode = "MCode1",
                Time = "Time2",
                Mkt = "Mkt2",
                Tkt = "SM2"
            },
        new CT()
            {
                MCode = "MCode1",
                Time = "Time2",
                Mkt = "Mkt2",
                Tkt = "SM2"
            },
        new CT()
            {
                MCode = "MCode1",
                Time = "Time2",
                Mkt = "Mkt2",
                Tkt = "SM2"
            }
    }.AsQueryable();

IQueryable<LM> lm = new List<LM>()
    {
        new LM()
            {
                MCode = "MCode1",
                Time = "Time1",
                Mkt = "Mkt1",
                MarketEsigi = "Mkt1"
            },
        new LM()
            {
                MCode = "MCode1",
                Time = "Time2",
                Mkt = "Mkt2",
                MarketEsigi = "Mkt2"
            },
        new LM()
            {
                MCode = "MCode1",
                Time = "Time2",
                Mkt = "Mkt2",
                MarketEsigi = "Mkt2"
            },
        new LM()
            {
                MCode = "MCode1",
                Time = "Time2",
                Mkt = "Mkt2",
                MarketEsigi = "Mkt2"
            },
        new LM()
            {
                MCode = "MCode1",
                Time = "Time2",
                Mkt = "Mkt2",
                MarketEsigi = "Mkt2"
            }
    }.AsQueryable();

var queryResult = from v in ve
                  join vax in vda on v.X equals vax.X
                  let ccc = ct.FirstOrDefault(c => c.Tkt == v.SM || c.Tkt == vax.SMSe)
                  join l in lm on ccc.Mkt equals l.MarketEsigi
                  where vax.Time == "Time2" && vax.MCode == "MCode1"
                  select v;

int totalSelected = queryResult.Count();


Let me know if it worked for you.
   
v3
Comments
Lalyka 19-Jul-15 3:41am
   
Thanks but there is no result :(
[no name] 19-Jul-15 12:11pm
   
please check first that your arms are growing from the right place. In the example that I've updaed, totalSelected after executing the query equals 12 rows. You can run the complete tutorial above on you PC to ensure that its working as expected.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100