Linq query to get the records of a table without using an object of the table

Question

I'm using entity framework 6 to develop my c# application. I have named my data model as Allocation model and I have a table called JobTable.

My Database model class looks like this

 public partial class  Allocation : DbContext
    {
        public  Allocation()
            : base("name=Allocation")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
        public virtual DbSet<JOB_Header> JOB_Header { get; set; }
    }

and my job header looks like this

My Job header class looks like this Job jeader class is the class generated from entity frame work model for my table Job_header

public partial class JOB_Header
    {
        public int JobID { get; set; }
        public string CustomerCode { get; set; }
        public string CustomerName { get; set; }
        public string MobileNo { get; set; }
        public string LocationCode { get; set; }
        public System.DateTime JobDate { get; set; }
        public bool Status { get; set; }
        public string Remarks { get; set; }
    }

How can I query data for sql queries like following.?

SELECT TOP 1 * FROM JOB_Header ORDER BY JOBID DESC;

select CustomerName from JOB_Header where JobID =1;



using (DataControllers.AllocationJAEntities = new DataControllers.Allocation())
            {
                JHeaderModel = JAEntities.JOB_Header.Where(a => a.JobID == 1).FirstOrDefault();
            }

Usually I get data for an object like above. but i need to get a single field without reading data to an object of the class created for Table in data model getting all row details for the object. How to handle a normal query in this way ?

score 4 · Answer 1 · answered Dec 05 '18 at 06:07

4

using (var context = new DataControllers.Allocation())
{
  var header = context.JOB_Header.OrderByDescending(j => j.JobID).FirstOrDefault();
}

Wasn't sure about your variable names so made my own

answered Dec 05 '18 at 06:07

Stephen Witherden

166
4

This seem to contradict `but i need to get a single field without creating object and getting all for the object` – TheGeneral Dec 05 '18 at 06:13
1

It is correct answer. But if you want to get single field, then modify code above such: context.JOB_Header.OrderByDescending(j=>j.JobID).Select(e=>e.FieldName).FirstOrDefault(); – Roma Ruzich Dec 05 '18 at 06:19
To be honest, I just looked at translating the SQL to Linq. We'll have to see how they go. – Stephen Witherden Dec 05 '18 at 06:20

score 2 · Answer 2 · answered Dec 05 '18 at 06:03

2

Why can't you select that field only then like below. Moreover, if your JobID is a key field (which it looks like) then I don't see the need for FirstOrDefault() at all since the Where() would up returning a single record only

JAEntities.JOB_Header.Where(a => a.JobID == 1).Select(x => x.CustomerName)

answered Dec 05 '18 at 06:03

Rahul

76,197
13
71
125

1

Who is doing all this weird voting – TheGeneral Dec 05 '18 at 06:14
@TheGeneral Someone who didn't understood the question clearly ... :) – Rahul Dec 05 '18 at 06:20
@TheGeneral, Rahul is correct. With above your query will be like - `SELECT name FROM Table Where ID = XX` and if you ever thought to use `find` method or thought about finding object first and then returning returning name, it will be like `SELECT * from table where ID = xx` hope its clear, – Deepak Sharma Dec 05 '18 at 06:35

Deepak Sharma · Answer 3 · 2018-12-05T06:31:25.287

when we want to get just the name you can do by below changes. The concept is when you are finding my KEY so there will be NO or One record at max. then -

string name = string.Empty;
using (DataControllers.AllocationJAEntities = new DataControllers.Allocation())
{
    name = JAEntities.JOB_Header.Find(1)?.CustomerName;
}

notice I used the Find method, as we are searching using primary key, else we can use WHERE also.

REMEMBER if you use Find actually it will query your DB for full row like below SQL query -

select * from table where Id = 1

mean you DB full row will be return for your specific ID, to your code, and then you are reading just your name property.

But when you want to achieve something like below SQL Query -

SELECT CustomerName FROM table WHERE Key = 1

for that case answer by Rahul is correct. -

string name = string.Empty;
using (DataControllers.AllocationJAEntities = new DataControllers.Allocation())
{
    name = JAEntities.JOB_Header
            .Where(a => a.JobID == 1)
            .Select(x => x.CustomerName)
            .FirstOrDefault();
}

To get the first record including order by you can (as Stephen mentioned above)-

using (var context = new DataControllers.Allocation())
{
  var job = context.JOB_Header.OrderByDescending(j => j.JobID).FirstOrDefault();
}

Linq query to get the records of a table without using an object of the table

3 Answers3