Translation of SQL query with INNER JOIN to Entity SQL query - C#

Question

I build the following SQL query dynamically:

StringBuilder query = new StringBuilder();
StringBuilder query2 = new StringBuilder();

if (ComboRuleType.Text.Equals("Standard"))
{
    query.Append("select * from [dbo].[" + ComboRuleTableName.Text + "]" + " WHERE" + "\n");
    query.Append("(" + "\n");

    for (int i = 0; i < dgvUpdateCriteria.RowCount; i++)
    {
        DataGridViewRow row = dgvUpdateCriteria.Rows[i];

        if (i != 0)
        {
            query.Append(row.Cells[1].Value.ToString() + " " + row.Cells[3].Value.ToString() + " ");
        }
        else
        {
            query.Append(row.Cells[3].Value.ToString() + " ");
        }

        if (row.Cells[4].Value.ToString().Equals("Contains"))
        {
            query.Append("like " + "'%" + row.Cells[5].Value.ToString() + "%'" + "\n");
        }
        else if (row.Cells[4].Value.ToString().Equals("Equals"))
        {
            query.Append("= " + "'" + row.Cells[5].Value.ToString() + "'" + "\n");
        }
        else if (row.Cells[4].Value.ToString().Equals("StartsWith"))
        {
            query.Append("like " + "'" + row.Cells[5].Value.ToString() + "%'" + "\n");
        }
        else if (row.Cells[4].Value.ToString().Equals("EndsWith"))
        {
            query.Append("like " + "'%" + row.Cells[5].Value.ToString() + "'" + "\n");
        }
    }

    query.Append(")" + "\n");
    return query.ToString();
}

After converting the above to Entity SQL, it looks like:

StringBuilder query = new StringBuilder();
StringBuilder query2 = new StringBuilder();

if (ComboRuleType.Text.Equals("Standard"))
{
    query.Append("select value q1 from ");
    query.Append(ComboRuleTableName.Text);
    query.Append("s");
    query.Append(" as q1 where " + "\n");
    for (int i = 0; i < dgvUpdateCriteria.RowCount; i++)
    {
        DataGridViewRow row = dgvUpdateCriteria.Rows[i];

        if (i != 0)
        {
            if (row.Cells[1].Value.ToString().Equals("AND"))
            {
                query.Append("&&" + " " + "q1." + row.Cells[3].Value.ToString());
            }
            else
            {
                query.Append("||" + " " + "q1." + row.Cells[3].Value.ToString());
            }
        }
        else
        {
            query.Append("q1." + row.Cells[3].Value.ToString());
        }

        if (row.Cells[4].Value.ToString().Equals("Contains"))
        {
            query.Append(" LIKE (" + "'%" + row.Cells[5].Value.ToString() + "%'" + ")" + "\n");
        }
        else if (row.Cells[4].Value.ToString().Equals("Equals"))
        {
            query.Append(" == (" + "'" + row.Cells[5].Value.ToString() + "'" + ")" + "\n");
        }
        else if (row.Cells[4].Value.ToString().Equals("StartsWith"))
        {
            query.Append(" LIKE (" + "'" + row.Cells[5].Value.ToString() + "%'" + ")" + "\n");
        }
        else if (row.Cells[4].Value.ToString().Equals("EndsWith"))
        {
            query.Append(" LIKE (" + "'%" + row.Cells[5].Value.ToString() + "'" + ")" + "\n");
        }
    }

    return query.ToString();
}

I construct another SQL query that contains INNER JOIN and I have looked EVERYWHERE but cannot find the equivalent translation of that SQL query to an Entity SQL query. I would really appreciate if you can help me out. The dynamic SQL query with INNER JOIN is as follows:

query.Append("SELECT * ");
query.Append("FROM [dbo].[membership] mm \n");
query.Append("INNER JOIN [dbo].[" + ComboRuleTableName.Text + "] xx \n");
query.Append("ON (mm.m_" + ComboRuleTableName.Text + "_id = xx.id) \n");
query.Append("WHERE xx.id IN ( \n");
query.Append("SELECT id from [dbo].[" + ComboRuleTableName.Text + "] \n");
query.Append("WHERE \n");
query.Append("mm.platform_name = '" + ComboRulePlatformName.Text + "' AND (\n");

for (int i = 0; i < dgvUpdateCriteria.RowCount; i++)
{
    DataGridViewRow row = dgvUpdateCriteria.Rows[i];
    if (i != 0)
    {
        query2.Append(row.Cells[1].Value.ToString() + " " + row.Cells[3].Value.ToString() + " ");
    }
    else
    {
        query2.Append(row.Cells[3].Value.ToString() + " ");
    }

    if (row.Cells[4].Value.ToString().Equals("Contains"))
    {
        query2.Append("like " + "'%" + row.Cells[5].Value.ToString() + "%'" + "\n");
    }
    else if (row.Cells[4].Value.ToString().Equals("Equals"))
    {
        query2.Append("= " + "'" + row.Cells[5].Value.ToString() + "'" + "\n");
    }
    else if (row.Cells[4].Value.ToString().Equals("StartsWith"))
    {
        query2.Append("like " + "'" + row.Cells[5].Value.ToString() + "%'" + "\n");
    }
    else if (row.Cells[4].Value.ToString().Equals("EndsWith"))
    {
        query2.Append("like " + "'%" + row.Cells[5].Value.ToString() + "'" + "\n");
    }
    else
    {
        query2.Append(" \n");
    }
}

query2.Append("))\n");
return query.Append(query2).ToString();

I NEED it to be in a string format. I later convert it from string to query format. I just do not know how the INNER JOIN syntax works with Entity queries.

Thank you.

Edit 1:

Here is how I convert that Query into Entity Framework Object Query:

                string query = EntityPreview(); //EntityPreview() is the method that gives me Raw Entity SQL Query
                var objctx = (context as IObjectContextAdapter).ObjectContext;
                if (ComboRuleTableName.Text.Equals("system"))
                {
                    ObjectQuery<system> standardList = objctx.CreateQuery<system>(query);
                    rulePreviewForm.dataGridViewCriteriaRulePreview.DataSource = standardList;
                    rulePreviewForm.Show();
                }

Yes. That is why I am using Entity Framework object context to run the Entity SQL Query. Can you help me out here? — Raza156, Apr 01 '20 at 20:10
Your problem isn't Raw Sql vs EF. Your problem is concatenating strings to build your query, which you appear to still be doing in your EF example. — devlin carnate, Apr 01 '20 at 20:12
When you mention Entity SQL are you referring to Entity Framework and that ability to create IQueryable types? — JWP, Apr 01 '20 at 20:12
I don't see any binding or LINQ for EF in code above. If it were EF we'd see IQueryable types. — JWP, Apr 01 '20 at 20:13
I edited my post. You can check how I convert the Entity SQL Query into Entity ObjectContext. I used this link for reference: https://www.entityframeworktutorial.net/Querying-with-EDM.aspx — Raza156, Apr 01 '20 at 20:15
@devlincarnate The SQL Query is constructed depending on the values inside a Windows Form TextBoxes and a DataGridView contained within the form. Is there an alternate way to dynamically get those values and not concatenate strings? — Raza156, Apr 01 '20 at 20:19
There isn't a safe way to concat user input to a query. You could employ parameters instead of string concat. Or, if the user input is predictable and not completely variable, you could use conditional statements to determine how you query the data — devlin carnate, Apr 01 '20 at 20:34
User input is very variable at this point in time. Can you please help me in converting the query into EF Query? The vulnerability error I was getting was when I was using SQLDataAdaptor. That is why I switched to EF SQL Query. — Raza156, Apr 01 '20 at 20:42
I think you should create two ObjectQueries an use a LINQ join between them. But why do you need this in text? You can use a predicate builder to add predicates to an expression. I would abandon this path if I were you. — Gert Arnold, Apr 01 '20 at 20:47

score 0 · Answer 1 · answered Apr 01 '20 at 21:14

One of the greatest things about EntityFramework is it builds SQL for you and allows you to manipulate objects instead of SQL. There are other libraries like Dapper that are quicker when using straight SQL if you have a choice. If you have to use EntityFramework, you would be better off writing Linq.

When using Linq instead of SQL, you can still build Dynamic queries using IQueryable. This allows you to build a query without pulling any data from the database up front.

Without knowing much about what you are trying to do with your application, I can only offer a few tips of things to try. In the answer below I am making some assumptions on the naming of how you have your entities set up.

For getting a list of memberships from the membership table, assuming your entity for that table is called Membership:

IQueryable<Membership> memberships = context.Memberships;

That is your

query.Append("SELECT * ");
query.Append("FROM [dbo].[membership] mm \n");

For your filters you will likely want to put them into a List.

From this point on is where the dynamic part of this comes in. If you have a table for your ComboRule named [ComboRuleTable1] and another called [ComboRuleTable2], but you have to query based on input from ComboRuleTableName.Text, you can do something like this.

var filters = new List<string>() { "name1", "name2" };

// Get which table you should join to
switch (ComboRuleTable1)
{
   // Join to tables and get filtered data
   case "ComboRuleTable1":
      memberships = memberships.ComboRuleTable1.Where(x => filters.Contains(x.PlatFormName));
      break;
   case "ComboRuleTable2":
      memberships = memberships.ComboRuleTable2.Where(x => filters.Contains(x.PlatFormName));
      break;
   default:
      break;
}

// This pulls the data from the database
var result = memberships.ToList();

Some of this will vary on how your EntityFramework is set up.

I hope this helps.

Translation of SQL query with INNER JOIN to Entity SQL query - C#

1 Answers1