locked
How to achieve the same with LINQ C# RRS feed

  • Question

  • User264732274 posted

    anyone can help me to write linq query for the below sql.

    just think my data is stored in datatable after issuing this sql select * from tridip_Interval

    now i want to run LINQ query on data table which would give me data same as above sql.
    i fumble to write right linq query because i hardly works with linq rather our company work with MSDAAB.
    any help would be appreciated. thanks


    when i just issue the sql select * from tridip_Interval then below output has come in sql server
    
    this is my full sql which i am using to get data.
    
    select 
    (select count(*) as incoming from tridip_Interval where direction='I' and CONVERT(datetime,right([Call Start],8)) >='08:30:00' 
    and CONVERT(datetime,right([Call Start],8)) <='09:00:00' and Is_Internal=0 and continuation=0 and 
    RIGHT(convert(varchar,[call duration]),8)<> '00:00:00' and party1name='Train5') as incoming, 
    
    (select count(*) as OutGoing from tridip_Interval where direction='O' and CONVERT(datetime,right([Call Start],8)) >='08:30:00' 
    and CONVERT(datetime,right([Call Start],8)) <='09:00:00' and Is_Internal=0 and continuation=0  and party1name not in ('Voice Mail') 
    and party1name='Train5') as OutGoing, 
    
    (select count(*) as CallTransfer from tridip_Interval where continuation=1  and CONVERT(datetime,right([Call Start],8)) >='08:30:00' 
    and CONVERT(datetime,right([Call Start],8)) <='09:00:00' and RIGHT(convert(varchar,[call duration]),8)<> '00:00:00' and 
    party1name not in ('Voice Mail') and party1name='Train5') as CallTransfer, 
    
    (SELECT count(*) as UnansweredCalls_DuringBusinessHours from tridip_Interval where direction='I' and 
    CONVERT(datetime,right([Call Start],8)) >='08:30:00' and CONVERT(datetime,right([Call Start],8)) <='09:00:00' 
    and RIGHT(convert(varchar,[call duration]),8)= '00:00:00' and [Ring duration]>0 
    and party1name='Train5') as misscall
    
    
    Monday, June 15, 2015 4:16 AM

Answers

  • User-271186128 posted

    Hi sudip_inn,

    As for this issue, please refer to the following code:

    SQL: select count(*) as incoming from tridip_Interval where direction='I' and CONVERT(datetime,right([Call Start],8)) >='08:30:00' 
    and CONVERT(datetime,right([Call Start],8)) <='09:00:00' and Is_Internal=0 and continuation=0 and 
    RIGHT(convert(varchar,[call duration]),8)<> '00:00:00' and party1name='Train5'
    
    To Linq: var incoming = from ti in tridip_Interval
                      where ti.direction == "I" || ti.CallStart.Value.AddMinutes(30).Hour >= 9 || ti.CallStart.Value.Hour <= 9 || ti.Is_Internal == 0
                       || ti.continuation == 0 || ti.callduration.Value.Hour > 0 && ti.callduration.Value.Minute > 0 && ti.callduration.Value.Second > 0 || ti.party1name == "Train5"
                      select ti.Count();
    
    
    SQL:select count(*) as OutGoing from tridip_Interval where direction='O' and CONVERT(datetime,right([Call Start],8)) >='08:30:00' 
    and CONVERT(datetime,right([Call Start],8)) <='09:00:00' and Is_Internal=0 and continuation=0  and party1name not in ('Voice Mail') 
    and party1name='Train5'
    
    To LINQ: var OutGoing= from ti in tridip_Interval
                      where ti.direction == "O" || ti.CallStart.Value.AddMinutes(30).Hour >= 9 || ti.CallStart.Value.Hour <= 9 || ti.Is_Internal == 0
                       || ti.continuation == 0 || !ti.party1name.Contains("Voice Mail") || ti.party1name == "Train5"
                      select ti.Count();
    
    

    Here are some relevant articles about LINQ query examples, please refer to them:

    https://msdn.microsoft.com/en-us/library/gg509017.aspx#BKMK_UsingDoesNotContainOperator

    https://msdn.microsoft.com/en-us/library/bb397927.aspx

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 17, 2015 10:20 PM