Converting sql statement that contains 'with' cte to linq

contains sql
sql check if string contains letters
sql string contains character
sql contains vs like
sql contains multiple values
case when contains sql
sql contains multiple words
oracle sql contains

I have this piece of code here, been battling with it for hours. basically what this sql statement does is gets ALL subfolders of a specified folder (@compositeId).

WITH auto_table (id, Name, ParentID) AS
(
SELECT
    C.ID, C.Name, C.ParentID
FROM Composite_Table AS C
    WHERE C.ID = @compositeId

UNION ALL

SELECT
    C.ID, C.Name, C.ParentID
FROM Composite_Table AS C
    INNER JOIN auto_table AS a_t ON C.ParentID = a_t.ID
)

SELECT * FROM auto_table

This query would return something like this:

Id   |    Name    | ParentId
1    | StartFolder| NULL
2    | Folder2    | 1
4    | Folder3    | 1
5    | Folder4    | 4

Now I want to convert the code to linq. I know it involves some form of recursion but still stuck thanks to the with statement.

There is no Linq to SQL equivalent that can do that (in an efficient manner). Best solution would be to call a SP/View/UDF from Linq containing that statement.

CONTAINS (Transact-SQL), SQL conversion functions allow you to do things like change a number in text and One is an SMALLINT value, which has no decimal places, and the other is We can use the CAST statement to convert the BirthDate into a VARCHAR value. SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

You could write code (recursive or not) that repeatedly queries the database, until it has all the results.

But I think there is no way to write a single LINQ to SQL query that would get all the results you need in one go, so it's probably best to keep the query in SQL.

Converting Between Data Types with SQL Server Functions , This articles explain the use of SQL Cast and SQL Convert functions in SQL In the output, we can see that the query with the style code of 1� Converting sql statement that contains 'with' cte to linq. Ask Question Asked 9 years, 2 months ago. Active 5 months ago. Viewed 3k times 5. I have this

There is known plugin 'LinqToDb', which provides methods to get CTE equivalent in Linq

SQL CAST and SQL CONVERT function overview, A very common T-SQL needs of database developer is to convert a or input to the tabular function, and used with the table in a T-SQL statement. A result-set table returned by this function contains every substring of the� CONTAINS SQL Function with other Databases. That is all you need to know to use COTAINS in your SQL statements with the SQL Server system. As I mentioned before, other databases may use the keyword as well, but they all use it differently. In these other systems, you need to use the LIKE statement to achieve the same results as above.

public static List<Composite> GetSubCascading(int compositeId)
{
    List<Composite> compositeList = new List<Composite>();

    List<Composite> matches = (from uf in ctx.Composite_Table
    where uf.Id == compositeId
    select new Composite(uf.Id, uf.Name, uf.ParentID)).ToList();

    if (matches.Any())
    {
        compositeList.AddRange(TraverseSubs(matches));
    }

    return compositeList;
}

private static List<Composite> TraverseSubs(List<Composite> resultSet)
{
    List<Composite> compList = new List<Composite>();

    compList.AddRange(resultSet);

    for (int i = 0; i < resultSet.Count; i++)
    {
        //Get all subcompList of each folder
        List<Composite> children = (from uf in ctx.Composite_Table
        where uf.ParentID == resultSet[i].Id
        select new Composite(uf.Id, uf.Name, uf.ParentID)).ToList();

        if (children.Any())
        {
            compList.AddRange(TraverseSubs(children));
        }
    }

    return compList;
}

//All where ctx is your DataContext

Converting commas or other delimiters to a Table or List in SQL , The datatype to convert expression to. Can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float,� The CONVERT() function returns the value of expression translated to the target_type with a specified style. The CONVERT() is similar to the CAST() function. However, it is specific to SQL Server. In contrast, the CAST() function is a part of ANSI-SQL functions, which is widely available in many other database products. SQL Server CONVERT

SQL Server CONVERT() Function, SQL LIKE Examples. The following SQL statement selects all customers with a CustomerName starting with "a": Example. SELECT * FROM Customers WHERE � SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

SQL LIKE Operator, SQL has several built-in functions with which you can convert integer to This SQL statement lists an item's name and quantity separately, each with its own� See the examples below for learning how to convert by using CAST and CONVERT functions. The example of string to int by SQL CAST. The CAST function in SQL can be used as follows: CAST ( expression AS data_type [ ( length ) ] ) Where the expression can be a text/string value, a number etc. that you want to convert into another data type.

Integer to Character Conversion in SQL, however there is one that has very sparse coverage: Using CONVERT() with CASE or IF So I decided that using a Case statement was the most ideal. as well" OR "If SQL Server is able to convert ANY of the values to the specified type, � First up: convert Excel to SQL using SQLizer. Step 1: Select Excel as your file type. Step 2: Choose the Excel file you want to convert to SQL. Step 3: Select whether the first row contains data or column names. Step 4: Type the name of the Excel worksheet that holds your data. Step 5: Input the cell range of the data that you want to convert

Comments
  • Yeah but I have to boycott all SPs and actual SQL code. No choice in this case
  • Why?! Why write bad code when you already have a good solution. You know that Linq generates SQL in the end anyway? I usually use Linq for most of my data access queries, but when high performance is needed I always write the SQL directly. Sql queries in code against your db context can map to Linq entities fine.
  • Ah, I definitely get your point now, thanks alot for the tip!
  • This will call the DB for every node in the tree structure.