CodeSamplez.com

Programming, Web development, Cloud Technologies

  • Facebook
  • Google+
  • RSS
  • Twitter
  • Home
  • Featured
    • C# Tutorials
      • LinQ Tutorials
      • Facebook C# API Tutorials
    • PHP Tutorials
      • CodeIgniter Tutorials
    • Amazon AWS Tutorials
  • Categories
    • Programming
    • Development
    • Database
    • Web Server
    • Source Control
    • Management
    • Project
  • About
  • Write
  • Contact
Home Database LinQ Query With Like Operator

LinQ Query With Like Operator

Rana Ahsan March 15, 2011 3 Comments


 LinQ Query With Like Operator    

Linq To SQL makes a developer’s life easy for performing various database operations. If the database design is stable, LinQ To SQL will do all the jobs for you for maintaining consistency between data relations. Simple data retrieval, insertion, deletion, update etc can be done in a very easy way , in some cases just by calling some functions on the LinQ objects. There are a number of ways are also provided for using functionality of a SQL operators/keywords. I will try to provide as much examples as possible on them time to time. For today, My main concern is to make you familiar with the ‘Like’ keyword and construct a easy alternative of SQL query with Like and LinQ together.

‘Like’ is a popular SQL syntax operator, that is used widely for performing search operations on database tables. Thanks To LinQ To SQL, we can now get this functionality with help of few ready given functions or even query syntax as well. They are as follows:

Check Keyword Existence Anywhere:

Suppose, Wan to match both sides(SQL Syntax: “%keyword%”), that means,
If we have a keyword and wants all results that contains this keyword and have anything prior/next to it, then, we can use the following function:

UsersDataContext myDB = new MyDataContext();
List<Users> users= (List<Users>)myDB .TVUsers.Where(u => u.Username.Contains(keyword)).ToList();

This generates the SQL Query Like as follows:

SELECT * FROM Users WHERE Username Like [%keyword%]


Read All LinQ Tutorials By CodeSamplez.com

Check Keyword Existence At Beginning/End:

Similarly, for matching the beginning and endings are as follows:

//matching first part of the column
List<Users> users= (List<Users>)TVDB.Users.Where(u => u.Username.StartsWith(keyword)).ToList();

//matching last part of the column
List<Users> users= (List<Users>)TVDB.Users.Where(u => u.Username.EndsWith(keyword)).ToList();

If you like to use LinQ’s SQL like statement syntax, sure you can use that too. Here is the examples of alternative SQL Like syntax:

//checks existence of a keyword
List<Users> temp = (List<Users>)from u in TVDB.Users
                                   where u.Username.Contains(keyword)
                                   select u;
//check records that start with the keyword
List<Users> temp = (List<Users>)from u in TVDB.Users
                                   where u.Username.StartsWith(keyword)
                                   select u;

//check record that end with the keyword
List<Users> temp = (List<Users>)from u in TVDB.Users
                                   where u.Username.EndsWith(keyword)
                                   select u;

Using LinQ To SQL Like As SQL Syntax:

Also, there is another more useful alternative ways to achieve this, even more handy that can be used to search with a regular expression pattern. That is provided by a .NET class, ‘SqlMethods’ (under ‘System.Data.Linq.SqlClient’ namespace). Is has a method named ‘Like'(with 2 overloads) to which you can send parameters with keywords/patterns. Here is an example of using ‘SqlMethods.Like’ for achieve the same result as above:

List<Users> users = (List<Users>)from u in TVDB.Users
                                   where SqlMethods.Like(u.Username,"%"+keyword+"%")
                                   select u;

They will generate sql statements respectively as follows:

SELECT * FROM Users WHERE Username Like [%keyword%]

SELECT * FROM Users WHERE Username Like [keyword%]

SELECT * FROM Users WHERE Username Like [%keyword]

You can easily observe that ‘Contains(keyword)‘ method doing the work of ‘%keyword%’, ‘StartsWith(keyword)‘ doing the task of ‘%’ and ‘EndsWith(keyword)‘ doing the task of ‘%keyword’. On the other hand, with SQL Methods, you can use those symbols exactly like SQL query.

Hope this small LinQ to SQL like operator tutorial helps you understand the use of ‘like’ operator along with linq queries more easily and efficiently. I will continuously try to provide more advanced tutorial on LinQ to SQL as much as possible. If you have any question regarding this tutorial, feel free to ask them by commenting here. Happy coding 🙂

Related

Filed Under: Database Tagged With: c#, linq

About Rana Ahsan

Rana is a passionate software engineer/Technology Enthusiast.
Github: ranacseruet

Comments

  1. codemake says

    June 30, 2012 at 1:53 pm

    okey very good but I want to

    my string “abc zxy 123” … n

    SELECT * FROM Users WHERE
    UserName LIKE ‘%abc%’ OR
    Name LIKE ‘%zxy%’ OR Name ‘%123%’

    … n

    ???

    Reply
  2. John G. says

    December 15, 2012 at 7:44 am

    Thank you! This is exactly what I was looking for. I am new to LINQ and find it great but just have to learn some of the ends and outs.

    Reply
  3. Luis Villarroel says

    January 28, 2019 at 10:15 am

    Thank friend

    Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Email Subscription

Never miss any programming tutorial again.

Popular Tutorials

  • PHP HTML5 Video Streaming Tutorial
  • How To Work With JSON In Node.js / JavaScript
  • How To Work With C# Serial Port Communication
  • LinQ Query With Like Operator
  • Generate HTTP Requests using c#
  • Utilizing Config File In C#.NET Application
  • How To Work With Codeigniter Caching In PHP
  • LinQ To SQL Database Update Operations In C#
  • Using Supervisord Web Interface And Plugin
  • Facebook C# API Tutorials

Recent Tutorials

  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read
  • Getting Started With Golang Unit Testing
  • Getting Started With Big Data Analytics Pipeline
  • NodeJS Tips And Tricks For Beginners
  • Apple Push Notification Backend In NodeJS
  • Web Based Universal Language Translator, Voice/Text Messaging App
  • How To Dockerize A Multi-Container App From Scratch

Recent Comments

  • S. Chalisque on PHP HTML5 Video Streaming Tutorial
  • Armorik on Generate HTTP Requests using c#
  • iswaps on PHP HTML5 Video Streaming Tutorial
  • TAKONDWA on PHP HTML5 Video Streaming Tutorial
  • rorenzo on PHP HTML5 Video Streaming Tutorial

Archives

Resources

  • CodeSamplez.com Demo

Tags

.net apache api audio aws c# cache cloud server codeigniter deployment doctrine facebook git github golang htaccess html5 http image java javascript linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty ssh tfs thread tips ubuntu unit-test utility web application wordpress wpf

Copyright © 2010 - 2022 · CodeSamplez.com ·

Copyright © 2022 · Streamline Pro Theme on Genesis Framework · WordPress · Log in