LINQ - example for subquery - Asked By chitra ganapathy on 20-Sep-11 02:46 AM

i need the example for subquery in linq...plz refer any link.

smr replied to chitra ganapathy on 20-Sep-11 02:50 AM

HI

Here's a subquery for you!

List<int> IdsToFind = new List<int>() {2, 3, 4};

db.Users

.Where(u => SqlMethods.Like(u.LastName, "%fra%"))

.Where(u =>

db.CompanyRolesToUsers

.Where(crtu => IdsToFind.Contains(crtu.CompanyRoleId))

.Select(crtu => crtu.UserId)

.Contains(u.Id)

)

refer
http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/6d0e74d8-7958-4d51-b0a2-53b2758bc934/

Suchit shah replied to chitra ganapathy on 20-Sep-11 02:59 AM

public void QueryMax()
{
    var queryGroupMax =
        from student in students
        group student by student.Year into studentGroup
        select new
        {
            Level = studentGroup.Key,
            HighestScore =
            (from student2 in studentGroup
             select student2.ExamScores.Average()).Max()
        };
    int count = queryGroupMax.Count();
    Console.WriteLine("Number of groups = {0}", count);
    foreach (var item in queryGroupMax)
    {
        Console.WriteLine("  {0} Highest Score={1}", item.Level, item.HighestScore);
    }
}

Suchit shah replied to chitra ganapathy on 20-Sep-11 03:00 AM

var query = from a in db.ONExport
	let ExportType = (from b in db.ONExportFlag
			  where b.nOnFlagID == 13
			  select b.sFlagValue).SingleOrDefault()
	let MultipleFileOutput = (from c in db.ONExportFlag
			  where b.nOnFlagID == 21
			  select b.sFlagValue).SingleOrDefault()
	select new
	{ 
		a.nONExportID, 
		a.nExportDescription, 
		a.sOutputFileName, 
		ExportType, 
		MultipleFileOutput 
	};

chitra ganapathy replied to Suchit shah on 20-Sep-11 04:43 AM

sir, in my following query
var fix1 = from future in fixture.Descendants("item").Take(4)
          select (new
          {
            titlee = future.Element("title").Value,
            venue = future.Element("text").Value
          });
i want to split the venue details, so i return as

            venue = future.Element("text").Value.Split(new char[] { ',' })

it split the data, but i dont know how to retrieve and assign to repeator control.

Suchit shah replied to chitra ganapathy on 20-Sep-11 08:46 AM

U dont need to assign the field just assign whole collection which u r getting

chitra ganapathy replied to Suchit shah on 21-Sep-11 01:45 AM

sir, in that venue result data, i want to split the date and details sepearately .

Neha Garg replied to chitra ganapathy on 21-Sep-11 01:27 PM

here is the simple example to write the Subquery using Linq....

First you need to use join...for Users and

 CompanyRolesToUsers tables



then



 "
selectedRoles.Contains(c.CompanyRoleId)
select u
"

from u in Users
join c in CompanyRolesToUsers 
       on u.Id equals c.UserId
where u.LastName.Contains("fra") &&
      selectedRoles.Contains(c.CompanyRoleId)
select u

Chintan Vaghela replied to chitra ganapathy on 12-Oct-11 04:28 AM

SQL

SELECT OrderID, OrderDate

FROM OrderTable

WHERE CustomerID = (SELECT CustomerID

FROM CustomerTable

WHERE City = “Seattle”)

Also note that because a query expression returns an IEnumerable, you can also query over query results:

VB

Dim SeattleOrders = From Contact In CustomerTable _

Join Shipment In OrderTable _

On Contact.CustomerID Equals Shipment.CustomerID _

Where Contact.City = “Seattle”

Dim FilteredOrders = From Shipment In SeattleOrders _

Select Shipment.OrderID, Shipment.OrderDate