CODEWITHSUNDEEP
c#entity-frameworklinq
JSON
JSON

Reputation: 1623

LINQ Select syntax returns all columns instead of selected ones

I m trying to select and return certain columns from a table instead, the query ignores the selection and returns all columns in addition to related fields in related table. How can I return only selected columns?

This my API Controller,

    [Produces("application/json")]
    [Route("api/Common")]
public class CommonController : Controller
{
    private readonly ArtCoreDbContext _context;

    public CommonController(ArtCoreDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public IEnumerable<IdState> GetState()
    {
        return _context.IdState;
    }

    [HttpGet("{id}")]
    public async Task<IActionResult> GetState([FromRoute] int id)
    {
        var  L2EQuery = await (from i in _context.IdState
                                      select new { Id = i.StaeId, i.text }).ToListAsync();

        return Ok(L2EQuery);
    }
}

Update

This is the model,

public class IdState
        {
            [DatabaseGenerated(DatabaseGeneratedOption.Identity), Key]
            public int StaeId { set; get; }
            public int CouyId { set; get; }
            [Display(Name = "State"), Required]
            public string text { set; get; }
            public ICollection<PatReg> State { get; set; }
            public ICollection<IdCity> TblIdCity { get; set; }

        }

and the output is,

[{
    "staeId": 1,
    "couyId": 2,
    "text": "California",
    "state": null,
    "tblIdCity": null
}, {
    "staeId": 2,
    "couyId": 2,
    "text": "WA",
    "state": null,
    "tblIdCity": null
}, {
    "staeId": 3,
    "couyId": 1,
    "text": "Ajloun",
    "state": null,
    "tblIdCity": null
}, {
    "staeId": 4,
    "couyId": 1,
    "text": "Amman",
    "state": null,
    "tblIdCity": null
}]

ajax call

@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
    <script src="~/ArtCoreScripts/Pages/ClinicCore/PatientRegistration.js"></script>
    <script>
        $(function () {
            $.ajax({
                type: "Get",
                url: "/api/Common",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    console.log(response);
                },
                failure: function (response) {
                    alert(response.responseText);
                },
                error: function (response) {
                    alert(response.responseText);
                }
            });
        });
    </script>}

Using LINQPad 5 I get the selected columns only,

LINQPad 5 Output

Upvotes: 0

Views: 297

Answers (3)

Bozhidar Stoyneff
Bozhidar Stoyneff

Reputation: 3634

There is no problem with your C# code, but the JavaScript.

You didn't specify the action within the controller, so it defaulted to the parameter-less HttpGet-enabled action, which doesn't project any fields. So the problem lies within your AJAX call. Correct it like so:

$.ajax({
    type: "Get",
    // Pay attention on the next line !
    url: "/api/Common/123",
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    success: function (response) {
        console.log(response);
    },
    failure: function (response) {
        alert(response.responseText);
    },
    error: function (response) {
        alert(response.responseText);
    }
});

"123" is just an example, but as I can see, currently it doesn't matter because you don't filter anything in your LINQ. However, you must specify it in the URL so it could be resolved to the correct method.

Upvotes: 3

Backs
Backs

Reputation: 24913

I think, you call method public IEnumerable<IdState> GetState() and it returns to you whole table.

Maybe, you need to call Task<IActionResult> GetState([FromRoute] int id)

Problem is here: url: "/api/Common", you calling wrong method, add parameter:

url: "/api/Common/123",

or maybe:

    $(function () {
        $.ajax({
            type: "Get",
            url: "/api/Common",
            data: {id: 123},
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                console.log(response);
            },
            failure: function (response) {
                alert(response.responseText);
            },
            error: function (response) {
                alert(response.responseText);
            }
        });
    });

Upvotes: 2

pankaj
pankaj

Reputation: 11

can you please try this

 public class CommonController : Controller
    {

        private readonly ArtCoreDbContext _context;

        public CommonController(ArtCoreDbContext context)
        {
            _context = context;
        }

        [HttpGet]
        public IEnumerable<IdState> GetState()
        {

            return _context.IdState;
        }
        [HttpGet("{id}")]
        public async Task<IActionResult> GetState([FromRoute] int id)
        {
            var  L2EQuery = await (from i in _context.IdState
                                    where i.test.equals("abc")
                                      select new { Id = i.StaeId, text= i.text }).ToListAsync();

            return Ok(L2EQuery);
        }
    }

Upvotes: 0

Related Questions