Please help me convert this sql to LINQ

Tag: linq

DECLARE @items table (
    pfid varchar(8),
    timestart datetime,
    timeend   datetime
)

insert INTO @items(pfid,timestart,timeend)
VALUES('123456','12:00 AM','3:00 AM')
,('987654', '2:00 AM', '4:00 PM')
,('492384', '3:00 PM', '9:00 PM')

SELECT * FROM @items a
    INNER JOIN @items b
        ON a.timestart < b.timeend
        AND b.timestart < a.timeend
        AND a.pfid != b.pfid

I need the select statement above converted to LINQ. In my code, I am working with a DataTable, named 'dt'. My table has three columns just like in the example above, exact same names and populated with this data.

I am struggling to create a LINQ query that will query my DataTable in the same fashion my SQL query is working with the temp table above. Please assist. Thanks in advance!

Best How To :

something in the line of...i didn't test this but you may have to do Datetime.Parse etc if needed...

(from r1 in dt.Rows.OfType<DataRow>()
 from r2 in dt.Rows.OfType<DataRow>()
 where r1["timestart"] < r2["timeend"] &&  r2["timestart"] < r1["timeend"] && r1["pfid"] != r2["pfid"]
 select new {R1=r1, R2=r2})

LINQ to Entities Retrieving related Entities or T-SQL

c#,sql-server,linq

you can try selecting from Vendors and using Any() for ShowId. Vendor[] vendorsInShow = (from v in db_.Vendor .Include("Products.Pics") .Where(m => m.Booths.Any(a => a.ShowID == showId) && m.Products.Count > 0) select v).AsNoTracking().ToArray(); ...

Passing conditional parameters to Database.SqlQuery

c#,sql,linq,entity-framework

You can edit your query to this SELECT * FROM Product p WHERE p.ProductId = @ProductId AND (@CustomerId IS NULL OR p.CustomerId = @CustomerId) Then you pass DBNull.Value to @CustomerId if it is not used...

How do I convert this tSQL statement to LINQ using group by in a sub query

c#,sql-server,linq,tsql

I think there is opportunity to rewrite your query, but for information purposes I rewrote your sql into linq verbatim. If you explain what you are trying to achieve we can provide alternative sql / linq var eqnums = new[] { "M0435", "Z0843" }; var testdate = "2008-06-01"; var query...

Listing directories by content size using C# [closed]

c#,.net,windows,linq

A small example here: class DirectorySize { public string DirectoryName; public long DirectorySizes; } public class Program { static void Main(string[] args) { string[] cDirectories = Directory.GetDirectories("C:\\"); List<DirectorySize> listSizes = new List<DirectorySize>(); for (int i = 0; i < cDirectories.Length; i++) { long size = GetDirectorySize(cDirectories[i]); if(size != -1) {...

ExecuteQuery returns empty collection when there is only a single result

c#,sql-server,linq

I don't know the exact issue you have, but it will of course return nothing if there are no table in the database. If you want to select all schemas better use: SELECT schema_id, name FROM sys.schemas ...

LINQ pad is showing SUM at end of result

linq,linqpad

LINQad will return results in its Results tab, any enumeration will have at the top of its blue box something like: IEnumerable<EventLogEntry> (114 items) Depending on the exact type. That count at the end is a LINQPad feature when it serializes the data for display, and it is the count...

Get List of Elements in Tree with specific Field Value

vb.net,linq,properties,interface

If i have understood it correctly you want to get all selected parents and all selected children. You could use a recursive method: Public ReadOnly Property checkedList As List(Of TreeSelectorAttributes) Get Return rootList.Where(Function(t) t.SelectedInTreeSelector). SelectMany(Function(root) GetSelectedChildren(root)). ToList() End Get End Property Function GetSelectedChildren(root As TreeSelectorAttributes, Optional includeRoot As Boolean =...

WPF, DataGrid, clicked item/row does not highlight (blue background)

c#,wpf,linq,datagrid

Here is the solution that works: private void tabControlOrganizer_SelectionChanged(object sender, SelectionChangedEventArgs e) { if (tabControlOrganizer.SelectedItem != null) { if (e.Source is TabControl) { if (tabItemTrades.IsSelected) { dataGridTrades.ItemsSource = Queries.GetTradeList(dataContext); } SelectionChanged was the problem: In C# WPF, why is my TabControl's SelectionChanged event firing too often? In free time I...

Using LINQ to reference XML local Xelements

c#,xml,linq

You always have the option to create extension methods to find elements by local name. Whether you like it or not, you have to "use an if within the foreach," that's an ovherhead you'll have to accept. This implementation is in terms of LINQ but you could always write it...

How to select rows from a DataTable where a Column value is within a List?

asp.net,.net,vb.net,linq,datatable

In the past, I've done this sort of like this: Dim item = From r as DataRow in dtCodes.Rows Where lstCodes.contains(r.Item("Codes")) Select r Does that work?...

Convert delimited string to array and group using LINQ in C#

c#,arrays,linq,csv

public static void Main() { var input = @"**Albert School**: George Branson, Eric Towson, Nancy Vanderburg; **Hallowed Halls**: Ann Crabtree, Jane Goodall, Rick Grey, Tammy Hudson; **XXX University**: Rick Anderson, Martha Zander;"; var universities = input .Split(';') .Select(ParseUniversity) .ToArray(); } public static University ParseUniversity(string line) { var split = line...

Finding an item in a list in c#

c#,linq

You have your condition wrong, you are looking for questionanswers where the question does not match, you should be looking where they do match and checking that the result is null. (switch the != with ==) It should be if (questionnaire.QuestionAnswers.Find(a => a.Question == fataQuestionsAnswers.Question) == null) However I would...

CSV File header part is coming parsing by LINQ

c#,linq,csv

You seem to be doing the Skip(1) in the wrong place: var csvLinesData = csvlines.Skip(1).Select(l => l.Split(',').ToArray()); // IEnumerable<string[]> As it stands you're skipping the first column for each row, not the first row....

Cannot get data using LINQ in MVC

c#,asp.net-mvc,linq,asp.net-mvc-4

Ensure term matches the case of the data. As all the data is loaded (.ToList() in the DAL), the .Where clause uses .Net comparison rather than SQL comparison: var vehicle = _service.GetAll().Where(c => c.Name.StartsWith(term, StringComparison.OrdinalIgnoreCase)... If, in the future, you want to change this to Contains, you can add an...

Difference between cast and as inside a select in LINQ

c#,linq,entity-framework

LINQ to Entities is not the same as LINQ to Objects. While LINQ to Objects functions can take any matching delegate and blindly invoke it as normal C# code, LINQ to Entities treats your lambdas as expression trees because it needs to understand the semantics of those lambdas (not just...

Why does .Where() with a Func parameter executes the query?

c#,oracle,linq,entity-framework

There is a very important difference between Enumerable.Where and Queryable.Where: Enumerable.Where takes a Func<T, bool>. Queryable.Where takes an Expression. Your filter variable is not an Expression, it is a Func<T, bool>, therefore, the compiler uses Enumerable.Where. What happens then is that all rows of your FOO table are transferred to...

Linq where clause with if condition

c#,linq

Something like this: .Where(m => !m.IsDeleted && (m.Project == null || !m.Project.IsDeleted)) You need to check if Project is null before checking if Project.IsDeleted. If you don't want the ones where project isn't null, then you just need: .Where(m => !m.IsDeleted && m.Project != null && !m.Project.IsDeleted) Remember: Shortcut evaluation...

Grabbing most recent transaction/record

c#,linq

One way would be filtering by startDate, grouping by ID, ordering by date, and grabbing the first element of the group: var res = tx .Where(tx => tx.TransactionDate <= startDate) .GroupBy(tx => tx.Id) .Select(g => g.OrderBy(tx => tx.Date).First()); ...

How does the Take() method work in LINQ

c#,.net,linq,entity-framework

See Return Or Skip Elements in a Sequence. Take(N) will add TOP N to your SQL and only retrieve N records. For example (using my own SQL Server 2014 with EF 6.1): This LINQ: var query = await dbContext.Lookup .Where(w => w.LookupCd == '1') .Take(10) .ToListAsync(); Generates this SQL: SELECT...

How to write Nested LINQ for specific scenario

c#,linq

This is a good example of something to do in Linq. You will find that you can do this easily and even more powerful queries if you understand two things: Don't try to overly literally translate from SQL into Linq. Lambdas. The first is easy to understand but hard to...

how to parse a rgbColor using c#

c#,linq

Easily accomplished with Regex: string colorStr = "rgb(67,134,215)"; Regex regex = new Regex(@"rgb\((?<r>\d{1,3}),(?<g>\d{1,3}),(?<b>\d{1,3})\)"); Match match = regex.Match(colorStr); if (match.Success) { int r = int.Parse(match.Groups["r"].Value); int g = int.Parse(match.Groups["g"].Value); int b = int.Parse(match.Groups["b"].Value); //// Create your new object with the r, g, b values } ...

Get a single text value using linq

c#,asp.net-mvc,linq,entity-framework,asp.net-mvc-3

Change your Select to be a Where. Where uses a predicate to filter the data and return the same structure...just a subset. Select on the other hand changes the data structure to be whatever is evaluated in the provided function. In your case you are changing the structure to be...

How to select all objects that have a property value in list of values?

c#,linq,linq-to-entities

You need to use Where with Contains: var selectedItems = db.Items.Where(x => locationIds.Contains(x.LocationId)); ...

Distinct all columns in a datatable and store to another datatable with LINQ

c#,linq

Solution 1: Simplest solution would be DefaultView.ToTable. dt.DefaultView.ToTable(true, "PONumber", "Customer", "Address"); Solution 2 : Another alternative would be using Linq statement to filter distinct rows and then looping them as required. var result = dt.AsEnumerable() .Select(row => new { PONumber = row.Field<string>("PONumber"), Customer = row.Field<string>("Customer"), Address = row.Field<string>("Address") }).Distinct(); Solution...

check if a list contains all the element in an array using linq

vb.net,linq

You can use Enumerable.All: dim linqMeddata = From m In medicineDataList Where keys.All(Function(k) m.MedicineData.Contains(k)) Order By m.MedicineName Ascending Select m ...

Sort a LINQ with another LINQ in MVC

sql-server,linq,entity-framework,controller

Try this: var materialnumb = (from r in db.MaterialNumber where r.MaterialNumber == 80254842 select r.MaterialNumber).FirstOrDefault(); var query = from r in db.SQLViewFinalTable where r.MaterialNumber == materialnumb select r But I can not get whay are you filtering by 80254842 and selecting the same value? You can do directly: var query...

Distinct() How to find unique elements in list of objects

c#,linq,distinct

You need to actually pass the IEqualityComparer that you've created to Disctinct when you call it. It has two overloads, one accepting no parameters and one accepting an IEqualityComparer. If you don't provide a comparer the default is used, and the default comparer doesn't compare the objects as you want...

How To optimise following linq to object query

c#,asp.net,linq,linq-to-objects

Well, couldn't you use a groupby ? var item3 = items.GroupBy (m => m.SKU) .Select(g => new { SKU = g.Key, colors = string.Join("," , g.Select(m => m.color_class) .Distinct()),//you could do a .OrderBy(m => m) after the distinct sizes = string.Join(",", g.Select(m => m.size) .Distinct())//you could do a .OrderBy(m =>...

Can't access any of Linq methods

linq,asp.net-web-api

The non-generic IQueryable interface doesn't have extension methods of Count and ToList(). Only the generic IQueryable<T> does. If you can modify the library to return a suitable IQueryable<T>, I suggest you do so. If you can't, you may need to call Cast<T>() or OfType<T>() to create an appropriate IQueryable<T> with...

Asp.Net Identity find users not in role

asp.net,linq,entity-framework,asp.net-identity

In c# you can get all users that are not in a certain role like this: var role = context.Roles.SingleOrDefault(m => m.Name == "role"); var usersNotInRole = context.Users.Where(m => m.Roles.All(r => r.RoleId != role.Id)); ...

How can I select items where there exists a reference between each item and all items in a list?

c#,linq

You need all the keywords to be present, so test keywords.All. Within that test for all, you need there to be a matching relevant keyword-reference, so test keywordReferences.Any: from f in forms where keywords.All( kw => keywordReferences.Any( kr => kr.KeywordId == kw.Id && kr.FormId == f.Id)) select f; ...

Adding where clause to IEnumerable Select

c#,asp.net-mvc,linq

Per Discosultan in the comments: IEnumerable<int> interestIds = viewModel.ExistingInterests.Where(x => x.selected == true).Select(x => x.InterestId); ...

Compare if the characters in a string are a subset of a second string in C#

c#,linq,string-comparison

This works for me: public static bool IsContainedWithin(this string @this, string container) { var lookup = container.ToLookup(c => c); return @this.ToLookup(c => c).All(c => lookup[c.Key].Count() >= c.Count()); } I tested it like this: var tests = new [] { "met".IsContainedWithin("meet"), "meet".IsContainedWithin("met"), "git".IsContainedWithin("light"), "pall".IsContainedWithin("lamp"), }; I got these results: True False...

sqlite query slow, how to optimize (using linq to entities)

c#,linq,entity-framework,sqlite

The biggest optimization would be changing the Contains to a StartsWith. Which would be equivalent to changing from name like '%search%' to name like 'search%'. Otherwise SQLite can't fully use the indexes you placed on the columns and you are basically searching the whole table. // First Name if (!string.IsNullOrEmpty(firstName))...

Error when building an XDocument

c#,xml,linq,xpath,linq-to-xml

You can ignore pretty much all your code, the issue is just this: XDocument people = new XDocument("Persons"); You can't create an XDocument containing a string, you need to add an element: XDocument people = new XDocument( new XElement("Persons", original.XPathSelectElements(xpathFilterDups))); ...

Please help me convert this sql to LINQ

linq

something in the line of...i didn't test this but you may have to do Datetime.Parse etc if needed... (from r1 in dt.Rows.OfType<DataRow>() from r2 in dt.Rows.OfType<DataRow>() where r1["timestart"] < r2["timeend"] && r2["timestart"] < r1["timeend"] && r1["pfid"] != r2["pfid"] select new {R1=r1, R2=r2}) ...

Sort Array by contains specific char count

c#,linq

Use: var myNewArray = myArray.OrderByDescending(u => u.Name.Split(' ').Length).ToList(); To count the number of words...

Count number of occurrences of each month in db table asp.net linq

c#,asp.net,linq,sql-server-2008

How about this? var dates = Enumerable.Range(1, 5).Select(i=>new DateTime(2015,i%3+1,1)); var grouped = dates.GroupBy(d => d.Month).Select(g => string.Format("{0}={1}", g.Key, g.Count())); // Now grouped will have your desired data (list of occurrences) I have used dummy dates here. You need to Select the date column from the database instead....

How to declare var datatype in public scope in c#?

c#,linq

Declare it as a known type (not an anonymous type), like this for example: Dictionary<int, string> results = new Dictionary<int, string>(); Then you could store the results in the Dictionary: results = behzad.GAPERTitles.ToDictionary(x => x.id, x => x.gaptitle); And reference it later: private void button1_Click(object sender, EventArgs e) { //...

Group by day, time and id in one LINQ

c#,linq

If you just want to group for displaying purposes, you don't need the classes GroupedByDay, GroupedByTime and GroupedById Considering examples is an IEnumerable<Example> var groupedExamples = from example in examples group example by new { example.SomeDate.Date, //Day example.SomeDate.Hour, // Hour example.Id // Id } into g select g; Then you'll...

LINQ Group By and not contains usage issue

c#,linq

It's not SQL but C#, so a string has quotes not apostrophes and it's && not and, change it to: where !line[12].Contains("VM") && !line[12].Contains("Voice Mail") ...

Update list of items in c#

c#,linq,list,updates

I would do something like this: (for ordinairy lists) // the current list var currentList = new List<Employee>(); currentList.Add(new Employee { Id = 154, Name = "George", Salary = 10000 }); currentList.Add(new Employee { Id = 233, Name = "Alice", Salary = 10000 }); // new list var newList =...

orderby () containing numbers and letters

c#,wpf,linq,linq-to-sql,sql-order-by

Given your example data where the first number is always a single digit: allScenes.OrderBy(x => x.SceneNumber).ToList() If you can possibly have multi-digit numbers, please provide them and where you want them in the sort order. This is one way to sort multiple digit numbers: var allScenes = new[]{ new {SceneNumber="4B"},...

Linq Conditional DefaultIfEmpty query filter

c#,linq,asp.net-mvc-5,linq-query-syntax

I solved it by adding the filter after the initial query, checking if the e.PractitionerProfiles were null. var query = from e in _repository.GetAll<Entity>() from u in e.Users where (e.AuditQuestionGroupId != null ? e.AuditQuestionGroupId : 0) == this.LoggedInEntity.AuditQuestionGroupId from p in e.PractitionerProfiles.DefaultIfEmpty() select new { entity = e, user =...

Updating entity framework model using Linq

c#,sql,linq,entity-framework

You need to update your SQL server with the following columns: UserActivity_UserID, Orders_UserId. Or in the code remove this two columns(Map again your DB to the edmx file)....

Issue displaying LINQ query results in a grid in C#

c#,linq

I solved my own problem. Here is what I did: Created a class for the attributes needed: public class dataRow { public string CenterName { get; set; } public string CenterCode { get; set; } public string NextImport { get; set; } public string NextExport { get; set; } public...

Linq update with specific select of column being null

c#,linq

The problem you have is that your model is not identical to you database, if you could select the row directly (you said Linq2SQL and not Linq2Entities) then you could update it again, which you do. If you select an anonymous type then you lose the context connection and cannot...

Load XML to list using LINQ [duplicate]

c#,xml,linq

Make a base class which will have id,x,y,z, and have Vendors,Bankers and Hospitals extend it. Then you can have a collection of the base class, and add to it the classes that inherit from it....

how can I add a column to IQueryable object and modify its values

c#,.net,linq,grid,devexpress

Simple example for using a non-anonymous class. public class MyLovelyClass { public Int32 Number { get; set; } public bool Selection { get; set; } } var packs = from r in new XPQuery<Roll>(session) select new MyLovelyClass() { Number = r.number }; gcPack.DataSource = packs; ...

joining two collections in C# or JavaScript

javascript,c#,linq,collections

There are couple of ways on how you can achieve that, i am guessing you want to use a json file as a source file, so why not convert everything to objects and deal with them that way, which will provide more flexibility to manipulate them and perform more complication...