How to execute raw SQL Query in my Dot net core project? Or how to translate it to Linq?

Question

I'm having a hard time finding anything about executing a raw query, I have no idea how to translate this to LINQ or if it is even possible.

I have ContactRecords in my DBSet but I don't really want that I guess, I just need to do multiple counts.. raw query..

I need to do multiple COUNTs and use a DB function i created "usp_Parse_domain_name_v5":

This is the Query I'm trying to execute in my DOT NET CORE app:

SELECT
    dbo.usp_Parse_domain_name_v5(ContactRecord.URL),
    COUNT(ContactRecord.ContactId) AS TOTAL_IMPRESSIONS,
    COUNT(DISTINCT ContactAttribute.ContactAttributeValue) AS UNIQUE_IMPRESSIONS,
    COUNT(DISTINCT LEADS.LEAD_ID) AS CONVERSIONS,
    MAX(convert(varchar(10), ContactRecord.CallStartDateTime, 102)) AS 'DATE'
FROM
    ContactAttribute 
    LEFT JOIN ContactRecord ON ContactRecord.ContactId = ContactAttribute.ContactId AND ContactAttribute.ContactAttributeName = 'IP'
    LEFT JOIN LEADS 
    ON LEADS.LEAD_ID = ContactRecord.ContactId
    AND LEADS.EMAIL IS NOT NULL AND LEADS.PHONENUMBER IS NOT NULL 
    AND LEADS.FIRSTNAME IS NOT NULL AND LEADS.LASTNAME IS NOT NULL
WHERE
    convert(varchar(10), ContactRecord.CallStartDateTime, 102) = '2019.05.16'
GROUP BY
    dbo.usp_Parse_domain_name_v5(URL)
ORDER BY 
    COUNT(ContactRecord.ContactId) DESC;

How about [this](https://stackoverflow.com/a/12679317/4228458) — CodingYoshi, May 19 '19 at 00:27

score 1 · Accepted Answer · answered May 18 '19 at 23:23

1

Why do you want to solve with linq? You can also write a stored procedure and call that. That is even easier. If query is too complex I always use strored procedures instead of linq. Here is an example; Stored Procedure in dot net core

answered May 18 '19 at 23:23

Y.Y.

664
1
6
22

Thank alot! I made it because of your comment, I put in my own answer of how I've done it with the stored procedure method. – Pablo Camara May 19 '19 at 00:44

score 0 · Answer 2 · answered May 19 '19 at 00:44

Alright, the selected answer by https://stackoverflow.com/users/7529020/y-y definitely helped me!

I created a model and added it to my context:

GatewaysImpressionsAndConversions

    public partial class GatewaysImpressionsAndConversions
    {


        public string DOMAIN { get; set; }
        public int TOTAL_IMPRESSIONS { get; set; }
        public int UNIQUE_IMPRESSIONS { get; set; }
        public int CONVERSIONS { get; set; }
        public string DATE { get; set; }
    }

created the Stored procedure too

and then all i had to do on the controller was:

 var result = _context.GatewaysImpressionsAndConversions.FromSql("GetGatewaysImpressionsAndConversionsByDay '2019.05.16'");
            return View("Statistics", await result.ToListAsync());

and in my view at the top: @model IEnumerable <YOURCONTEXT.Models.GatewaysImpressionsAndConversions>

and then the loop:

 <table class="table" style="display: none" id="results">
        <thead>
            <tr>
                <th>
                    Domain
                </th>
                <th>
                    Total Impressions
                </th>
                <th>
                    Unique Impressions
                </th>
                <th>
                    Conversions
                </th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in Model)
            {
                <tr>
                    <td>@item.DOMAIN</td>
                    <td>@item.TOTAL_IMPRESSIONS</td>
                    <td>@item.UNIQUE_IMPRESSIONS</td>
                    <td>@item.CONVERSIONS</td>
                </tr>
            }
        </tbody>
    </table>

Thanks alot! If it wasn't for @Y.Y's answer I would be hitting my head on a wall right now.

Hope this helps someone else in need,

Best regards

How to execute raw SQL Query in my Dot net core project? Or how to translate it to Linq?

2 Answers2