Nested Select queries I main select query LINQ

Question

I hardly use LINQ rather I use MSDAAB as DAL which I use to execute my inline sql query or my db store procedure etc. Now I am in situation where I need to use LINQ to extract data from datatable.

Here I am attaching one screenshot like how my data look like:

The data is coming from sql server and for which I am using the below query for sql server. Please see my query first.

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

Now I want to use LINQ to query data table but hence I am not good in linq so not being able to compose the above sql like query with linq. So just wonder if anyone can help me to compose the above query with linq.

I apologize if could not highlight my linq query because very honestly I know linq very little and that is the reason things is not coming to my mind how to compose the same above sql query with LINQ. Looking for hep and support.

Answer 1

Linq, the process, is simply to target a distinct data listing.

The first step is to use an ORM (Object Relation Mapper) such as Entity Framework (EF). With EF doing the mundane mappings of tables and stored procedures into entities one can focus on displaying the data or processing it.

Hence that is now where Linq comes in, for EF will make each of the tables a list of data.

---

I would recommend that to learn Linq one of the best tools out there is a developers professional editor named Linqpad. With Linqpad (with premium features of object intellisense) it is hands down the best tool to craft linq queries.

How does one go about learning Linq?

Start with just taking 10 entities of the table you are doing and run the tool. Look at the data. Then add a .Where( ) clause and look at the data displayed again. Note there is a tool specific extention on Linqpad .Dump() which can show the data.

Learn about Select, it is a projection extension. Projection means one will project the current data listing into something new. Could be a new anonymous entities, or a new list of instances of a particular class one wants to return.

Keep adding to the query until you have created the dynamic entity which you want to achieve.

But I work mostly in SQL...

Still think in SQL?

Linqpad will show you the generated SQL which was executed by Linq to achieve its results. Also one can run SQL queries against the database! How about one window with the original SQL query and one window Linq to help one learn.

That benefit might give one insights on developing Linq queries.

It is how I taught myself to think in Linq and I have never looked back.

---

I recently revealed how to use stored procs and EF to achieve the ultimate goal in linq in my article entitled Entity Framework Stored Procedure Instructions.