CSharp/C#, Convert sql to linq?

microsoft.public.dotnet.languages.csharp - General discussion regarding the C# .NET programming language. including best practices on developing with C# in VS, documentation, setup, and samples.


CSharp/C# >> Convert sql to linq?

by Andy » Tue, 02 Sep 2008 21:39:34 GMT

Hi,

I have a sql statement I'd like to convert to Linq to Sql. Here's the
sql:

select sum( SubTotal), DocumentCategory
from vDocumentCategory
where DocumentType = 'I'
and CreatedDate between '1/1/2006' and '12/1/2006'
and DocumentStatusId = 1
group by DocumentCategory

Thanks
Andy
Top

CSharp/C# >> Convert sql to linq?

by Marc Gravell » Tue, 02 Sep 2008 21:52:59 GMT


Well, hard to tell without reproducable code, but it is going to be
something like:

var qry = from category in ctx.DocumentCategories
where category.DocumentType == "I"
&& category.CreatedDate >= start
&& category.CreatedDate <= end
&& category.DocumentStatusId == 1
group category by category.Category into grp
select new
{
Category = grp.Key,
SubTotal = grp.Sum(x => x.SubTotal)
};
Top

CSharp/C# >> Convert sql to linq?

by Pavel Minaev » Tue, 02 Sep 2008 22:00:32 GMT


from dc in vDocumentCategory
where dc.DocumentType == 'I' && dc.CreatedDate >= new DateTime(2006, 1, 1)
&& dc.CreatedDate <= new DateTime(2006, 12, 1) && dc.DocumentStatusId == 1
group SubTotal by dc.DocumentCategory into g
select new { DocumentCategory = g.Key, Total = g.Sum() }
Top

CSharp/C# >> Convert sql to linq?

by Andy » Tue, 02 Sep 2008 22:06:04 GMT

That works, thanks!
Top

CSharp/C# >> Convert sql to linq?

by Marc Gravell » Tue, 02 Sep 2008 22:45:22 GMT

[Pavel replied...]


You'd need to select the specific property to sum, otherwise you are
summing classes


Watch for i18n - I have no idea whether the OP meant December 1st or
January 12th (hence I cheated and said neither...)

Marc
Top

CSharp/C# >> Convert sql to linq?

by Andy » Wed, 03 Sep 2008 20:41:34 GMT

heh.. the date was just an example anyway. The real query has
variables in place of the hard code values I specified.
Top
Similar Threads

1. Convert SQL to linq (automatically) - ADO.Net

2. In Orcas Linq, When does it happen to convert LINQ to SQL command 

Ho folks, 
         In Orcas LINQ,  when does it happen to convert LINQ to SQL command? 
 Waht role does the conversion - runtime, compiling time...and where is the 
SQL command? Please advise. Thanks.
PeterK

3. Combination of Linq to sql and Linq to xml - CSharp/C#

4. Linq to SQL - Return DataTable as a result of Linq query 

Hi!

How to get result od dataTable from Linq query? I have typied DataSet
and I want to join couple of tables. And I have a problem with change
this result to DataTable type. (I don't want to rewrite everything in
foreach)

e.g. How can I make DataTable from it? Thx for help!

 var query =
from firmy in boKontakty.DataSetKontakty.FIRMY
                      join kancelarie in
boKontakty.DataSetKontakty.KANCELARIE on firmy.FIRMA_O equals
kancelarie.ID into firmKan
                      join kategorie in
boKontakty.DataSetKontakty.KATEGORIE on firmy.KATEGORIA equals
kategorie.NAZWA into firmKanKat
                      select
                      new
                      {
                          Id = firmy.ID,
                          Nazwa = firmy.NAZWA,
                          Nazwa_pelna = firmy.NAZWA_PELNA,
                          Kancelaria_obslugujaca = kancelarie.NAZWA,
                          Kolor = kategorie.KOLOR
                      };

5. Mixing linq to sql and linq to xml (in linqpad) - CSharp/C#

6. Question about LinQ (LinQ to Sql)

7. Linq 2 sql, enum, no supported translation to SQL - CSharp/C#

8. LINQ to SQL + SQL Server 7 

Hello,

I am trying to work with MS SQL Server 7 from the release version of
Visual Studio 2008 + LINQ to SQL. And the problem is that the LINQ to
SQL designer doesn't accept my tables saying that my connection
provider is unsupported. Then I found out that LINQ to SQL officially
supports only .NET Provider for SQL Server. The problem is that SQL
Server 7 can't work with this provider, so I have to use the .NET
Provider for OLE DB. Well, at least, ServerExplorer can't connect to
SQL Server 7 using .NET Provider for SQL Server...

Now the interesting thing is that this is a designer's problem only!
Actually LINQ to SQL do can communicate with SQL Server 7 without any
problems, including inserting data. I've implemented my data context
without using the designer, as follows:

class MyDataContext : System.Data.Linq.DataContext
{
   public System.Data.Linq.Table<CUSTOMER> CUSTOMERs;

   [System.Diagnostics.DebuggerNonUserCodeAttribute()]
   public MyDataContext() :
      base(new System.Data.SqlClient.SqlConnection("Data
Source=10.1.1.1;Persist Security Info=True;Password=111;User
ID=111;Initial Catalog=DEVELOP_DEKEL_PUBLISH"))
   {
   }
}

Please note that I am using here System.Data.SqlClient.SqlConnection.
And this works excellent.
The only problem is that I do that manually, I can't use the
designer...

So, ServerExplorer can only connect to the db using .NET Provider for
OLE DB while LINQ itself can use the SQL Provider without any
problems. And therefore the only thing that I need to do is to
convince the LINQ designer that it actually CAN handle my db =)

Can anybody offer some idea?