.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

How to write the LINQ query for the following

Posted By:      Posted Date: August 30, 2010    Points: 0   Category :ASP.Net
Hello All, I have an entity type with the following properties: UnitId, UnitDescription, ParentId, EffectiveDate - (Say the entitty type is Unit)  I want to filter out a list of UnitIds whose effective date is less than a given date. But since there could be many EffectiveDate s less than a given date, I want to take the latest one(Max). Simply told, I want to get the unit ids with the most recent effective date prior to a given date. How can I do this in a single linq statement? Appriciate any help.

View Complete Post

More Related Resource Links

How to write SQL Insert into query using LINQ


How I can write following SQL query using LINQ. There is no default constraint define for the ID column so we have explicitly pass the NEWID().

I want to replicate the products of a company to another company.

Insert Into Products
Select NEWID() AS ProductID, ProductName, CompanyID = @DestinationID, ProductCategoryID From Products Where CompanyID = @SourceID

Also once I get these result how can I use InsertAllOnSubmit to insert all the records at once without looping it.

How do I write this kind of Linq query?



I have this kind of class:

class A{
  public int a;
  public int b=0;

and I have this list: List<A> l;

for example. data in this l is: {a=1,b=0},{a=2,b=0},{a=3,b=0},{a=1,b=0},{a=3,b=0}

Now I would like to find out every A in l, that if there are more than two A that have same value of a, then their b should all be set to 1.

In this case, the list of l should finally like: {a=1,b=1},{a=2,b=0},{a=3,b=1},{a=1,b=1},{a=3,b=1}

because there are two A have value 1 of a, and two A have value 3 of a, only the second A in the list has unique value 2 of a.

Please teach me how to write this kind of linq query.


Need help with a linq to sql query


I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:

Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5

Need Help in LINQ query for group By with chunks of record


I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&

Query an arraylist of array list using linq


I have an array list called Customer. I t holds a list of array list which has the account details.

proj.customer -> collection of arraylist of type customer

proj.customer.account -> collection of arraylist of type account

Account array list holds type account and it has a poperty account balance. How can I query all accounts which has account balance  >100

I tried the following one

var Accountlist= from Account accountdetails in proj.customer.account

where accountid.balance >100

select accountdetails.id

But it is always throwing an error saying cannot cast type customer to type account

Please help

Basic Instincts: Increase LINQ Query Performance


Jared Parsons demonstrates a technique to achieve maximum performance from LINQ queries on large sets of data in order to create a responsive user interface.

Jared Parsons

MSDN Magazine August 2008

Data Points: Standard Query Operators with LINQ


LINQ to Objects and LINQ to Entities have an arsenal of Standard Query Operators that operate on sequences to perform a wide variety of operations.

John Papa

MSDN Magazine March 2008

Help with SQL query to Linq (VB.Net)



I am facing problems converting a relatively complex SQL query to a LinqToSQL statement. There are lots of examples on the web, but none involve 'joins' + 'where' + 'group'

Here is my query I am trying to use Linq for :

select Customers.CustomerID,          
       count(*) as NumOfOrders,         
       sum(Customers.BillingAmount) as BillingAmount,      
       sum(Orders.OrderTotal) as OrderTotal  
From Customers 
left outer join Orders on Customers.CustomerID = Orders.CustomerID
Where Customers.Name = @username	  
      and Customers.LastLoggedIn > @fromdate	  
      and Customers.LastLoggedIn < @throughdate	  
      and Customers.ZipCode = @zipcode 
group by Customers.CustomerID

I need help converting this to a LinqToSql for VB.Net

Thank you very much!

Linq query to DataTable


Is there option to convert from Linq.IQueryable to DataTable with some sort of CopyToDataTable() function. I am trying this.
     var row = from p in dc.ProductModels
                  select p;
    DataTable dt = row.CopyToDataTable();

Is there option to convert from Linq.IQueryable to DataTable with some sort of CopyToDataTable() function. I am trying this.

     var row = from p in dc.ProductModels


Linq query results to List collection



I have a GridView in my page. I want to use below code in code beind. query nothwing DB using linq . add linq query result to new list and then bind list as gridview Datasource

I use below code . But I don't know how to add results to List? Could any one tell me how !

Public Class OrderCity
        Private _name As String
        Private _city As String
        Public Property name As String
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value

            End Set
        End Property
        Public Property city As String
                Return _city
            End Get
            Set(ByVal value As String)
                _city = value

            End Set
        End Property
    End Class
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim dbe As New SFFAV1DataContext
        Dim latestadsDs = From p In dbe.Products _
                            Select New With {p.Title, p.ProducedCity} _
                             Take (2)

        Dim FilteredList As New List(Of OrderCity)()
        For Each result In latestadsDs
            Dim Menedfcou = Nothing
            FilteredList.Insert(0, Menedfcou)

LINQ query with multiple joins, problem


I am using a LINQ query with multiple joins, the last join does not return any values even though values exist in the database. Below is my code.

when the query returns suiteNameTrg and SuiteTypeTrg are empty, all other values are returned correctly.

string suiteNameTrg = string.Empty;
            string suiteTypeTrg = string.Empty;
            using (DataClassesDataContext db = new DataClassesDataContext())
                    var productQuery = from assets in db.ASSETs
                    join relocatableUnits in db.RELOCATABLE_UNITs on assets.RUID equals relocatableUnits.RUID into assets_units
                    from relocatableUnits in assets_units.DefaultIfEmpty()
                   join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins
                   from build in assets_bins.DefaultIfEmpty()
                   join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins
                   from test in test_bins.DefaultIfEmpty()
                    join testTrgt in db.TEST_SUITEs on assets.TARGET_TEST_SUITE_ID equals testTrgt.TEST_SUITE_ID into testTrgt_bins
                    from testTrgt in testTrgt_bins.DefaultIfEmpty()

                    select new

Dynamic Class with Linq query Result

Hi, var   query = db.Customers.Where( "City == @0 and Orders.Count >= @1", "London", 10). OrderBy( "CompanyName"). Select( "New(CompanyName as Name, Phone)");    I am using this query as a sample which returns a dynamicclass type, and i need this should to converted to a list collections or datatable etc., As i am holding it in my wcf service with linqtosql as dataaccesslayer. Trying to send the results to the UI which i have consumes this service. Kindly help me in this. Thanks query = db.Customers

Linq Query Union

HelloI need to use Union for below Linq queries. I could use Union it if it was simple "select new whith{" but Now that I Specified Class, I encounter Error using unionDim CStep() As New List(Of ProductModel) CStep= (From p In db.Produts _ Where p.CategoryID= 1 _ Select New ProductModel() With { _ .ID = p.ProductID, _ .Name = p.Title, _ } Take (5)).Union (From p In db.Produts _ Where p.CategoryID= 2 _ Select New ProductModel() With { _ .ID = p.ProductID, _ .Name = p.Title, _ } Take (5)).ToList()

How to write query with subquery more efficient

Hello, I have this query which generates sick days. I have a sick start date and end date, and by joining it with a date table I get all sick days for each employee. But as you can see i select a number of fields, but in the subquery i select a number of fields which are the same. Is it possible to write it more efficient? Thans in advance! SELECT z .ziekmeldingID, z .hrmID, z .ZiekStartDatum, z .ZiekEindDatum, z .ZiekStartTijd, z .ZiekEindTijd, z .ZiekOpen, z .ZiekTypeID, z .ZiekRedenID, z .ZiekStart_KEY, z .ZiekEind_KEY, z .Verzuimduur, D .Datum AS ZiekDag,   CAST(CONVERT(char(8), D.Datum, 112) AS int) AS ZiekDag_KEY, z .hr301_date, z .PercentageZiek, z .PercentageHersteld FROM     (   SELECT dbo .hr300.hr300_obj AS ziekmeldingID, dbo .hr300.hr300_date_start AS ZiekStartDatum,   CASE   WHEN dbo.hr300.hr300_date_end IS NOT NULL   THEN dbo.hr300.hr300_date_end   ELSE getdate()   END AS ZiekEindDatum, dbo .hr300.hr300_time_start AS ZiekStartTijd, dbo .hr300.hr300_time_end AS ZiekEindTijd, dbo .hr300.hr300_open AS ZiekOpen, dbo .hr300.hr104_obj AS ZiekTypeID, dbo .hr300.hr038_obj AS ZiekRedenID, dbo .hr300.hr200_obj AS hrmID,   CAST(CONVERT(char(8), dbo.hr300.hr300_date_start, 112) AS int) AS ZiekStart_KEY,   CAST(CONVERT(char(8), dbo.hr300.hr300_da

Need to add hard coded values to LINQ query result

I am running a LINQ query which populates a list used by a DropDownList, I need to insert an "Unassigned" value to the list. The function below queries the values correctly from the db but does not insert the "Unaasinged" value to the list. Please help.   public List<KeyValuePair<int, string>> GetTestList() { KeyValuePair<int, string> item = new KeyValuePair<int, string>(0, "Unassigned"); using (DataClassesDataContext db = new DataClassesDataContext()) { var Query = from records in db.TEST_SUITEs orderby records.TEST_SUITE_ID select new KeyValuePair<int, string> ( records.TEST_SUITE_ID, string.Format(records.TEST_SUITE_ID + " " + records.SUITE_TYPE + " " + records.SUITE_NAME) ); //Query.ToList().Insert(0, item); Query.ToList().Insert(0,item); return Query.ToList(); } }  

Check if Linq query return result

HelloI have Linq query likeDim Prood= (From p InProdutcts _                                          where p.ProductID="InputInteger"                                                         ..... ) .SingleOrDefaultI could Use Count if I Use .ToList Instead of .SingleOrDefault , But now that I use 'SingleOrDefault', how Could I find if Prod contains any record or it's Empty.

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend